AnsweredAssumed Answered

Finding, Summing, Deleting Duplicate Records, then Insert Totals into a Field

Question asked by user25682 on Mar 13, 2017
Latest reply on Mar 14, 2017 by user25682



I have an odd issue for which I can’t seem to figure out the correct search terminology. Hopefully, someone can see through my “been staring at it for far too long” haze and point me in the right direction.


I have to run a report based on data that’s imported from an online ERP solution which will then get imported into a software package as an Excel file. The report must have one line per item, per location in the final form. No changing any of that.


That’s easy, however there are some physical locations that have one item stored in more than one box. For those locations, I need to summarize the total quantity per unique ID, but then generate a list of the former quantities in each Box, sorted in ascending order, and put that in one field of the report.


For example, in pseudo CSV form….


In Nevada, we might have Qty 10 of Item A1001, in Box B, therefore the row would read:

NV-A1001, 10, B(10)


In Utah, we have Qty 50 in Box B and Qty 25 in Box A. The imported records would be:

UT-A1001, 50, B(50)

UT-A1001, 25, A(25)


I need to get that down to one record that goes a little something like this:

UT-A1001, 75, A(25)-B(50)


Then, spit out the report like this:

NV-A1001, 10, B(10)

UT-A1001, 75, A(25)-B(50)


Any thoughts on this? Right now, I can’t figure out a way to find the duplicated records that doesn’t involve the impossible task of searching on a Summary field.


I've calculated the product code ("NV-A1001") and the BoxQuantity ("B(50)") in the table that receives the import.


Thank you in advance!