Union Tool Missing Records

Question

Hi SME,

I'm so confused about why I couldn't get all the records in my Union tool in the workflow below. The first Text tool is the data input. The second Text tool is where I expect to see all the output for my data schema. I used the Append Fields tool to initiate my data to zero. Then I used the Join tool to connect to the original data and my Append Fields output to get the full dataset. But I couldn't get the full dataset.

I have 3 work orders with 49 records each meaning that I expect to see 147 records in the total from the Union tool. Instead, I got 91. Could you please help me figure out why this is the case?

Also, I'm unable to use the Append Fields tool if I were to get rid of all the work orders except one. It kept giving me an error, not sure why.

Thanks,

kwl

Union Issue.yxzp

Qiu · Accepted Answer

@knnwndlm 
I think there is a problem with your setting about the Join tool.

Using only the Name will cause many to many Joining, instead we should add Key with the Work Order Number and BK  Account as belwo.
After that  the record number is 147 now.