Business Event to create reclassification entry in Acumatica - Part 1

I’ve ran into this specific requirement at least on three different occasions. I’ve had to do it for Equipment Time Card entries to Projects, and something related to Landed Cost. This last instance was due to Inventory Issues on Service Orders posting to the default sub account on the Service Order Type and not the default account from the Inventory ID configuration (or Posting Class).

In this situation I’ve seen users manually identifying the original entries and then doing an adjustment journal entry. The idea in what we’re going to build is it will identify the entry and then give an “Action” to make the adjustment entry. Saves a lot of time for the user and removes some room for error.

I first start by building a Generic Inquiry looking for the original entries. In this case I’m using the INTran table. Again, just like in my previous blog post you can identify the table you want to use by using Inspect Element. I would do this in Inventory Issue screen where I’m reviewing the transaction I’m trying to fix. In addition to identifying the table, the other thing I can do is actually review the data itself from the raw table. This helps with visualizing the fields we need to use.

Inspect Element from Inventory Issues opens this screen. From here you click on the Data Class.

The Data Class hyperlink opens the DAC Schema Browser. This will show all the fields, their type and length. From here, I can also select Source Data.

You can filter the Document Type and the Reference Nbr to find your specific transaction in here.

When I reviewed the data in the INTran I was able to see the sub accounts used during posting. I verified this to the Financial Batch Nbr generated and visible from the Finance tab of the Inventory Issue. The fields I wanted to focus on are the cOGSSubID which was the incorrect subaccount during the posting.

I also want to source the sub account it should have gone to which was on the InventoryItem table field invtSubID. Again, I identified this with my Inspect Element function (Ctrl+ Alt while clicking on a field) to identify the field from the Stock Item screen.

Adding the InventoryItem table, I used a cool feature that was released in 24R1 that let Acumatica create the joins for you automatically when selecting the table.

Highlight your anchor table and select “Add Related Table”.

Find the table you want to link, in this case InventoryItem, then click “Select Related Table”, this will fill in the Relation area. By selecting Add, it will add the table to Data Sources but also create the Relationship between the tables. This is often a barrier for people who do not have database backgrounds so this was a very nice addition by Acumatica.

I was now able to visualize on a linear level, the original transaction, I had the date, the Amount and the sub accounts, including the subaccount I wanted it to reallocate to.

Now onto the new challenge: I want to structure this as if it was a journal entry, this means I need a Debit and a Credit, for the same amount, and on the Debit line I want to use one subaccount and the Credit the other subaccount. I learned a cool trick from the Acumatica Community a while back. There’s a table called PMReportsRowMultiplier. The table basically consists of 2 rows, one is Row 1 and the other is Row 2.. This table is only available if you have Projects or Construction. In the past when I did not have this table I used Unit of Measure and created a special unit called CONV and had a 1 and 2. This let me filter down and get the same result. Maybe we’ll revisit that work around in the future.

This lets me add the table so every entry is duplicated in my generic Inquiry.

After adding the PMReportRowsMultiplier table, I do not create a Relationship. This will automatically duplicate my line entries. For visualization you can add the RowNumber field to help with this next part.

I created a formula for the Sub Account, the Debit Amount and the Credit Amount. I also wrote down on a pad what account needed to be the Debit and the Credit so I could make sure I was doing this right.

For my Debit Amount I made a Calculation where it looks to see if the Row Number is 1 and then gets the TranCost field, otherwise it’s 0

=iif([PMReportRowsMultiplier.RowNumber]='1',[INTran.TranCost],0)

I did the same for Credit Amount but with a 2.

=iif([PMReportRowsMultiplier.RowNumber]='2',[INTran.TranCost],0)

Now came my next issue. I used a similar formula for my Sub Account.

=iif([PMReportRowsMultiplier.RowNumber]='1',[InventoryinvtSubID],[INTran.cOGSSubID])

When I looked at my results, because of this formula, it did not show me the Sub Account but the back end Sub Id. So this is a good and bad thing. In Acumatica some of the fields when they’re used on the GI visualizes themselves how we would expect to see them but the field itself is actually the backend field value. So when I was visualizing the field without a formula I saw the Sub Account value, and now I don’t because that logic isn’t recognized in the formula.

This led me needing to bring in the SubAccount table and connecting it to these fields. I actually needed to bring in the table twice so that one maps to the InventoryItem table and the other to the INTran table. When you do this you can give each table an alias. This helps with identifying the tables when you link them and use them in formulas.

Same Table is brought in twice but given a unique alias.

Because I have to do this manually and not using the “Add Related Table” button I mentioned earlier, I have to go to the Relations tab and create the relationship.

Linking INTran to the Sub Account table I used the Alias of TranSub

And the same for InventoryItem

Now that I have these tables I was able to adjust my formula to the following.

=iif([PMReportRowsMultiplier.RowNumber]='1',[InventorySub.SubCD],[TranSub.SubCD])

The result gives me the Debit and Credit line I needed. I went back to the Result Grid in the Generic Inquiry and cleaned up the fields I didn’t need.

The resulting Entry I want to make in Journal Transactions with the ADM incorrect subaccount being Debited and the correct BID subacocunt being Credited.

Looks like this will be a 2 parter! In the next part I’ll go over turning this GI into a Business Event with a manual trigger to create the Correcting Entry Action.