Trying to get a complete history list of inventory balance and quantities can be difficult. The IN.INItemCostHist table only contains a value if the inventory balance has changed from the previous period and is missing rows if the period did not contain have any transactions.
For example, the AMPCB item did not have any transactions for the 08-2019 period. Therefore, if you just selected the inventory Cost History table for the 08-2019 financial period, the AMPCB would be selected and you would have an incorrect history balance.
The AMPCB item did not have any transactions for the 08-2019 period. Therefore, if you just selected the inventory Cost History table for the 08-2019 financial period, the AMPCB would be selected and you would have an incorrect history balance.
I know this has been an issue for many Acumatica users.
Fortunately, there is a way to solve this problem. There is a supporting table, IN.INItemCostHistByPeriod, that has the following fields:
The LastActivityPeriod field will allow us to get the correct ending balance.
You can see from the data in that IN.INItemCostHistByPeriod that there is an entry for each financial period that references the last activity period. We can use this information to create a generic inquiry that will give us records for all periods with the correct balance.
First, we need to reference IN.INItemCostHist twice as seen below.
On setup of the relations tab, in the first use of IN.INItemCostHist, we link the finPeriodID to the corresponding finPeriodID (see below):
We use the second instance of of IN.INItemCostHist with a different join key; we use the lastActivityPeriod to join to the IN.INItemCostHist.
Then, with the use of the isNull function, we can display the value from the current period if one exists or the last period with a value (which obviously has not changed) as the value for the period that we had missing before.
The Generic Inquiry now has the correct balance for the missing 08-2019 period.
Stay tuned for more tips to get the most out of your investment in Acumatica.
