There are multiple “pricing” tables or other tables that works similar. I.e. VatRate, Salary or Wage Revision, Purchase Invoice and Business Policy table. Rates or Purchase prices may change at certain dates. In those tables we use STARTDATES and ENDDATES. We thought it the best way since if ever historical documents is called up it will use those dates again to return the correct price or rate for that day. There are more challenges, i.e. if a supplier was initially not registered for vat, but register later, our setup has a checkbox whether a supplier registered for VAT, when the box is not checked, no vat is raised. The challenge is when checking the box when they register later, historical invoices will be affected and it should not. It happens very seldom but it does, we have to bring in STARTDATE and ENDDATE again. During time we thought of multiple reasons to store invoices with pricing and VatRates not connected to “RATE” Tables. We found reasons and preferred the strategy where rates of all stored invoices and other stored documents like payslips are connected to “RATE”tables, if ever a historical document is called up the STARTDATE and ENDDATE is needed.
Maybe an expert can give me a better opinion on our strategy.
In the image I posted three Enddates, can be seen in a table.
Enddate17. When an item is revised it means a new record is entered. Currently we have to enter the Enddate17 when an item is revised. This thread is about his date.
Enddate18. Calculated field which stores Enddate19 if no date in Enddate17. This date is mainly used as EndDate everywhere.
EndDate19. Some date in the extreme future since we still have to learn of an expression or a way to use an open ENDDATE?
I hope to be understood here. Our forms that we fill in in the event of a change of any of these many various items needs an ENDDATE. When we complete these forms, we have to go back to the previous form to enter the ENDDATE. We need help to find VBA or any way where when the previous ENDDATE can be filled in as soon as the new form is completed. On the image I posted see item POL000003. It was revised on 1Dec2017 see POL000006. We had to edit form POL000003 manualy to enter ENDDATE. On the new form POL000006 the ENDDATE is now open although as you may see we use 31dec5000.
My question for this thread is. We want an automatic way for the ENDDATE to be filled in on form POL000003 when POL000006 is completed. The ENDDATE currently is one day before the STARTDATE on the new form. Purchase of the same product may happen twice on the same day. We will have to use time as well in that case. If purchase of an Inventory item happened 14May2018 11H00 then 14May 15H00 our system must provide for that.