Test a field’s values against a list

I have a large dataset (Dataset 1) that has a field called “Form Name”. I want to test the dataset to make sure all the records have known Form Names. Dataset 2 is just a list of the valid Form Names. The point of this is to prevent another group from adding forms and not notifying my group.

How can I test the field, Form Names, in Dataset 1 to ensure that each record has a known Form Name listed in dataset 2?

IF NOT [Form Name from dataset1] IN (‘Form1’,’Form2’,’Form3’) from dataset2

Additionally, if there are records in dataset1 with Form Names not in dataset 2, I’d like to

create an output file (Browse Tool or Excel) that contains the offending Form Name
stop the workflow
show me a message “Out of Scope Forms” or something.

I have looked at the test tool and can’t really make any sense of it.

I have considered the filter tool, but I don’t know how to filter against a second dataset list. I guess I could hard code the entire list in the filter tool, but I was hoping there was a cleaner way.

Thanks in Advance

Datasets

Accepted answers

Christina_H

Just seen that you also want to know which forms didn't match, so I'd update the workflow to this:

This uses the message tool to let you know about the problematic forms, and stops further processing if there are any.

All comments

binu_acs

@ScottC_00 Have you tried using the find and replace tool or join tool?

ScottC_00

Thanks binuacs.

I am not sure how the find/replace tool helps. I will investigate that tool a bit more.

In the meantime the Join tool worked fine for part 1.

It identifies an unknown Form Name in the data against a list.

But what about part 2?

Is there a way to stop the workflow and open a mssg box if count of unknown forms >0?

Thanks again

Christina_H

It sounds like the Test tool is what you're looking for.

This returns an error if there are any forms from the left join that don't have a matching entry from the right, although I don't think it stops further processing. However you could use a Count Records tool to count the left output, append it to the rest of the data, then filter out everything when the count isn't zero.

Christina_H

Just seen that you also want to know which forms didn't match, so I'd update the workflow to this:

This uses the message tool to let you know about the problematic forms, and stops further processing if there are any.

ScottC_00

Christina,

Thank you so much.

I will look into the message tool and the append fields tool.

This looks like it will do exactly what I am looking for.

Christina_H

It sounds like it would be worth combining the message tool with the workflow properties setting in that article. The message can be configured as an error, and you can set the workflow to stop on error.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703