Test a field’s values against a list

[ScottC_00]

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

1. create an output file (Browse Tool or Excel) that contains the offending Form Name
2. stop the workflow
3. 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

[binuacs]

@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

[ScottC_00]

I found this article

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Stop-workflow-on-a-condition/ta-p/1...

which describes how to stop the workflow when an error is found.

But it seems to just stop without notifying me of the error.  I have to open the test tool to see the offending Form.

Any way to create a pop up message?

Thanks

[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 · Accepted solution]

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]

I did some testing and reread the article a few times and finally got it to work.  
The pop up message I was looking for is in the notes.
Thanks

[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.