hi
how can i filter parts sort by last arrival dates on filtering window . when i filter 100 part no at same time, shows me all dates and take so long. i dont wanna see all dates, i want to see only last records each part numbers.
Solved
how can i filter products sort by last arrival dates
+1Best answer by ShawnBerk
You don’t need to do any grouping, that is just going to take longer and not give you the answer you want anyway.
Searching on Purchase Orders or using SQL also isn’t necessary as you want to see things actually received, not the unreceived purchases. Receipts History is the right place according to what you are intending to find.
My suggestions for limiting the data set is first to limit by Site (this only matters if the part is in more than one site). If you only have one site, then this doesn’t matter.
The second parameter that you definitely need is to limit the dates over which you are looking this will significantly speed up the search as it will reduce the data set to more recent records. For most of the part numbers I would assume you’ve purchase them in the last 2 or 3 years, so I would add this parameter in the condition for the Actual Delivery Date:
>#TODAY#-720 (This will look in the previous 2 years of data as a rolling look backwards)
>#TODAY#-1000 (This will look in the previous 3 years of data)
You will have to choose how far back you want to look. If you don’t find any results within 3 years, only then will you need to clear out the condition for the date to look at all of the parts.
Hopefully that makes sense, but I’m sure it will help speed up your search.
+23Hi
in which screen do you want do that?
Usually you can use “Sort by”. For example:
Hi, if i understood your question, just select Highest in grouping option:
But when you group, you must specify which field to see and you must ensure one of them does not disturb the grouping, same as GROUP BY option on SQL.
+1
i want to see which supplier we buy last time and how much we pay. i need only last date cos when filter many product, its take too long.
+1Hi, if i understood your question, just select Highest in grouping option:
But when you group, you must specify which field to see and you must ensure one of them does not disturb the grouping, same as GROUP BY option on SQL.
when i group columns it doesnt work, still shows me all records cos i need to see supliers,price and quantity columns too
+23i want to see which supplier we buy last time and how much we pay. i need only last date cos when filter many product, its take too long.
Hi
this screen shows you the history of parts in certain orders. Therefore it can’t show you only the first two rows if you search by the part no only. Part no is not unique here. You need more details in your search.
If you need columns that are specific to each line such as the price, then yes, it will not work with simple grouping. You must first perform a subquery to isolate the items with max date and the key columns and then make an INNER JOIN to the main table to retrieve all remaining columns for these pre-selected items. That is not possible as far as i know from the SQL tab in the advanced search as it only proposes a where clause. You could easily achieve this through a Quick Report but you may lose some right click functionalities. If that is a must for you to get it as a view and not as a report, then you can create a custom logical unit with all required columns and a schedule task that regularly refresh this list, then you can add custom menu to it.
After a 2nd thought, something like that could work:
I’m not an SQL expert, but assuming the SEQ_NO is your key column (just a dummy example), if you manage to get a pre-selected list of items with the max receive date in the sub query behind the IN function, then it should work.
You don’t need to do any grouping, that is just going to take longer and not give you the answer you want anyway.
Searching on Purchase Orders or using SQL also isn’t necessary as you want to see things actually received, not the unreceived purchases. Receipts History is the right place according to what you are intending to find.
My suggestions for limiting the data set is first to limit by Site (this only matters if the part is in more than one site). If you only have one site, then this doesn’t matter.
The second parameter that you definitely need is to limit the dates over which you are looking this will significantly speed up the search as it will reduce the data set to more recent records. For most of the part numbers I would assume you’ve purchase them in the last 2 or 3 years, so I would add this parameter in the condition for the Actual Delivery Date:
>#TODAY#-720 (This will look in the previous 2 years of data as a rolling look backwards)
>#TODAY#-1000 (This will look in the previous 3 years of data)
You will have to choose how far back you want to look. If you don’t find any results within 3 years, only then will you need to clear out the condition for the date to look at all of the parts.
Hopefully that makes sense, but I’m sure it will help speed up your search.
+1Thank you for all advice. i searched about 800 part no at same time and wait too longer, export to excel and filter there.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.