Check if a multiple fields contain valid data

[Newtoalteryx]

Hi all!

I have dataset 'A'  lets say a list of IDs. Now I have 4 fields in my data called Primary Ap ID, Secondary Ap ID, Primary Al ID and Secondary Al ID. I want to check if all the 4 IDs in my data are present in my dataset A.

If all the IDs are correct (present in A) then I want all ID fields to be output.

Primary Ap ID	Secondary Ap ID	Primary Al ID	Secondary Al ID
123	890	786	654

If both the primary IDs are present and the secondary IDs are wrong then I want to only output the Primary IDs.

Primary Ap ID	Secondary Ap ID	Primary Al ID	Secondary Al ID
123	890

If either of the primary ID are wrong then I want to output 4 additional fields for each ID. And I want the output to look like this.

Primary Ap ID	Secondary Ap ID	Primary Al ID	Secondary Al ID	Primary Ap ID Issue	Secondary Ap ID Issue	Primary Al ID Issue	Secondary Al ID Issue
122	890	786	654	Yes	No	No	No

How can I accomplish this? 

I have the dataset A in an excel sheet.

Thanks for your help!

[Rhys_Cooper]

Hi @Newtoalteryx. Thank you for your question. Please find my solution method and attatched workflow below. Remember to remap the input and output tools to the appropriate locations.

1. 

    add "no" to all rows in Dataset A. This is "no" in the sense that there is no issues.

2. Next, there is a sequence of four join + union combinations. Here, for each ID column it trys and joins to Dataset A. For each target ID column, a new column is created with the ID column name as the field, and then a value of "no" if its contained in Dataset A, and Null if it wasnt.
3. As mentioned, this is repeated for all 4 columns in the input data set.
4. Multifield formula tool replaces all nulls with empties to match your desired output
5. Create two forks to implement your desired logic. The top one filters for if primary and secondary ap id was found. The second one filters for If either of the primary ID are wrong, and creates the requested additional four columns stating if there was an issue or not
6. I included two different output options: one unioned file containing both forks, with nulls in the additional 4 columns if that row was contained in the first logic fork or Yes or No for the rows in the second logic fork. The second is the two forks outputed as seperate sheets.

Please try the workflow attatched to check it meets your requests and let me know if you need any help with ammendments.

kind regards - Rhys