Creating Unique ID's

[ethankutch]

Hello,

I am trying to create Unique External ID's for records in a sample accounting based dataset. 

In my workflow, I have used a formula tool to create my current External ID's : "QBO" + [Date] + ToString([Debit]) + ToString([Credit])

This usually works because every external ID needs to be unique not to just one record, but the records with the related debits and credits (see records 59 & 60). Since the debit is null on the credit line and vice versa, this usually pairs the two when also combined with date. 

However, in some instances (see records 55-58), there are identical debit/credit amounts for two transactions (55-56 is one, and 57-58 is the other), when combined with the same date as well, this creates an non-unique ID for the two transactions that should be separate.

My first thought was to somehow use the unique tool to identify duplicates and then somehow parse on extra numbers on two debit and credit records that are in the dups. However this seems to me like a flawed way of doing it that might not always work for more variable datasets (say I had 3 matching transaction sets and not just 2). Does anyone have any better ideas?

(See images for context, first is normal data, second those same records in the duplicate section of a unique tool)

Thanks!

[mmueller7337]

Perhaps this is simplifying things too much, but at first glance, it would seem your Memo/Description fields may be somewhat unique. Would it be possible to use a Summarize tool to group by Date and Group by Memo/Description, then apply Record Ids, then Join based on the Date and Memo/Description fields?

[Anthony_Giesing]

You could try to add a daily transaction ID using the Multirow tool. It looks like the dataset you receive has Credits of the transaction always listed before the Debits so something around that may work. I was able to make a quick proof of concept of that below:

[ethankutch]

Hey @Anthony_Giesing , thanks for the response! That may work, but I still need to keep associated debits and credits matched with each other. For instance in your example, records 1 & 3 or 2 & 4 would need to have the same ID's, but different from the other pair.

[Anthony_Giesing]

Makes sense, That was more for set up for testing to see if multiple debits or credits in a transaction would function properly. I added another multi-row formula tool to the end to group the items.

[jdminton]

I was going to suggest the descriptions as well in this specific scenario, but having worked with many accounting systems, there are likely other fields that can be brought in to help as well. You might want to explore the system/report to see if you can get other information like journal entry number. This appears that it may be quick books online. There should be journal entry numbers available.

[ethankutch]

@mmueller7337 @jdminton thank you guys for your responses! In this case, memo is often unique, but not always. There are instances where the memo from record to record is exactly identical, and additionally this data have no journal entry numbers. This leaves records that are exactly the same, but need to be different. So I'm probably going to have to create new fields I believe.

[mmueller7337]

@ethankutch If your data originates in a sorted format, and your entries are "together" from the onset, what about adding a column to attribute a + to debits and a - to credits, then add a Running Total based off that "t-account" value. Then add a Record ID tool, followed by a multi-row formula for as many rows as you can conceive an entry of possibly consuming? I presume if your data is sorted as Entry 1, then Entry 2, etc. when it is exported, then this solution would create a new "record number" in the last column whenever an entry "sums" up to zero. Revised proposed solution attached.