Just about had a major report completed when a monkey wrench was put into my report. I am now caught with determining the best route to go on the data logic here.
It was as easy as selecting a record from the Inventory Location file which stores fields as LastRecvdQty and Last RecvdDate for purchase orders received for that item number to that warehouse location. So therefore there wouldhave beenonly one matching record for an item in this file. Match the item number and the warehouse location and then print the lastrecvdqty and lastrecvddate in the specified columns. Each time a purchase order is received their their POP system it will update these two fields for that particular item number and warehouse location. But now here is the monkey wrench. This is a new system and when they brought over all the inventory items from the old system instead of them going in as purchases and then hitting the inventory files they came over as inventory adjustments. Now as the system ages when an item is received actually thru their POP system the proper fields are updated with the information. But the problem I have is that there are many items that are very slow moving or discontinued and will not be receiving more of that particualar item for a long time or ever again. The report purpose is to monitor the movement of the inventory in an aging result. Therefore the dates of receipt on new inventory as well as the non-movement of slow moving items is just as if not more important. Since the inventory was brought over to the new system as Inventory Adjustments I need to use either two different files to determine most recent receipt date and qty or one file being that of the inventory transaction history file. The inventory transaction history file keeps track of ALL MOVEMENT for an inventory item, not just it's receipts.
This is where I need to decide how to handle this. Upon execusion of this report the Selection will be that if an item has not been received between the current date and the user parameter date it is to print the information on that item. Am I better off testing the one file first that will hold the lastrecvddate and lastrecvdqty, determining if their is a value here and then if not move on to the inventory transaction history file and grabbing the inv adj for that item when it went into the new system. Also keep in mind that even if it is a fast moving item and they have had receiving for that item since the new system the new receiving information will also be in the inv transaction history file.
So I am working with two files; #1 inventory location file and #2 inventory transaction history file.
If an item has been received thru their POP system since the new system went live their will be a value in this file/field of lastrecvdqty and lastrecvddate. If there has been no receiving since their system went live then using the inventory transaction history file I will need to pull one of the many records to determine the transfer of the inventory from the old to the new sytem. One thing is that they used two specific dates. All inventory that was received from their old system prior to Jan 1 2002 went in with only one date of 12/31/01 and all inventory received thru their old system up to the live date went in as 10/20/02. It seems that these entries are the very first entries in the file for each specific item. So my options tells me then I am better off working with one file and that is the inv transaction history file. Or looking at the item location file, determining if the lastrecvddate is equal to 00/00/00 and then going to the inv transaction history file to pick up the inv adj for that item. Although the transfer of the inventory to this history file is the first record I do need to determine if their has been further receiving since this item was transfered to the new system. This is where I am having difficulties in figuring the formula for this.
If the field lastrecvddate in item location file is 00/00/00 then use the inv trans history file and pick up the first record if the transaction date is equal to 12/31/01 or 10/20/02, but then if their has been receiving since that item wentinto the new sytem further in the records in this file will be the new(most recent) reveiing information. But by that time if it has been received into the new system thru POP then the field in the item location file will be populated with a date and qty.
A rought formula would be something like this maybe:
If {itemloc.lastrecvddate} = 00/00/00 then
{invtrnshst.transdate} (this would be the very first record in the file for this item)
Or would I be better off using a selection/case formula.
This would have been so much easier if it has gone in as a purchase receipt instead.
Thanks for the helphere and I am sorry if this is a bit confusing.
Thanks
Mark
Email: markanas333@hotmail.com
It was as easy as selecting a record from the Inventory Location file which stores fields as LastRecvdQty and Last RecvdDate for purchase orders received for that item number to that warehouse location. So therefore there wouldhave beenonly one matching record for an item in this file. Match the item number and the warehouse location and then print the lastrecvdqty and lastrecvddate in the specified columns. Each time a purchase order is received their their POP system it will update these two fields for that particular item number and warehouse location. But now here is the monkey wrench. This is a new system and when they brought over all the inventory items from the old system instead of them going in as purchases and then hitting the inventory files they came over as inventory adjustments. Now as the system ages when an item is received actually thru their POP system the proper fields are updated with the information. But the problem I have is that there are many items that are very slow moving or discontinued and will not be receiving more of that particualar item for a long time or ever again. The report purpose is to monitor the movement of the inventory in an aging result. Therefore the dates of receipt on new inventory as well as the non-movement of slow moving items is just as if not more important. Since the inventory was brought over to the new system as Inventory Adjustments I need to use either two different files to determine most recent receipt date and qty or one file being that of the inventory transaction history file. The inventory transaction history file keeps track of ALL MOVEMENT for an inventory item, not just it's receipts.
This is where I need to decide how to handle this. Upon execusion of this report the Selection will be that if an item has not been received between the current date and the user parameter date it is to print the information on that item. Am I better off testing the one file first that will hold the lastrecvddate and lastrecvdqty, determining if their is a value here and then if not move on to the inventory transaction history file and grabbing the inv adj for that item when it went into the new system. Also keep in mind that even if it is a fast moving item and they have had receiving for that item since the new system the new receiving information will also be in the inv transaction history file.
So I am working with two files; #1 inventory location file and #2 inventory transaction history file.
If an item has been received thru their POP system since the new system went live their will be a value in this file/field of lastrecvdqty and lastrecvddate. If there has been no receiving since their system went live then using the inventory transaction history file I will need to pull one of the many records to determine the transfer of the inventory from the old to the new sytem. One thing is that they used two specific dates. All inventory that was received from their old system prior to Jan 1 2002 went in with only one date of 12/31/01 and all inventory received thru their old system up to the live date went in as 10/20/02. It seems that these entries are the very first entries in the file for each specific item. So my options tells me then I am better off working with one file and that is the inv transaction history file. Or looking at the item location file, determining if the lastrecvddate is equal to 00/00/00 and then going to the inv transaction history file to pick up the inv adj for that item. Although the transfer of the inventory to this history file is the first record I do need to determine if their has been further receiving since this item was transfered to the new system. This is where I am having difficulties in figuring the formula for this.
If the field lastrecvddate in item location file is 00/00/00 then use the inv trans history file and pick up the first record if the transaction date is equal to 12/31/01 or 10/20/02, but then if their has been receiving since that item wentinto the new sytem further in the records in this file will be the new(most recent) reveiing information. But by that time if it has been received into the new system thru POP then the field in the item location file will be populated with a date and qty.
A rought formula would be something like this maybe:
If {itemloc.lastrecvddate} = 00/00/00 then
{invtrnshst.transdate} (this would be the very first record in the file for this item)
Or would I be better off using a selection/case formula.
This would have been so much easier if it has gone in as a purchase receipt instead.
Thanks for the helphere and I am sorry if this is a bit confusing.
Thanks
Mark
Email: markanas333@hotmail.com