After going through bunch of different solutions on community I decided to post here as I didn't find the right solution for my task.
I have one file into which I need to assign 'City' values from below table by matching its values from the below table. The values which starts with '<>' indicates not equal to. If all the values of a row matches from the below table then respective 'City' values should be assigned.
Production Facility Code
<> HFG2653 YTF
<> DGE878 TYRD
<> FGDS2154 HFGH
I am not sure how can i tackle it more efficiently other than creating different sets of rules and making complex workflow. I have attached the sample file with File 1 sheet contains data in which I need to add city based on conditions provided in File 2 sheet. Thanks in advance.
i've attached a workflow that should work for you.
the main part of the strategy i used was to create "criteria" fields that i can use in a formula downstream. since we can't "negatively" join on fields in the same join tool, we instead need to join on one field that is always true (or append if you really have all fields that differ in logic). Then you can apply a formula (or sets of filters) to create the logic needed to append the city names.
here's the formula i created in a single filter tool:
//zip code test
(//zip code = right zip code AND the requirement to not have the zip code match is false
([Zip Code]=[Right_Zip Code] && ![NOT_Zip Code])
//or zips don't match and we want that ("Not_zip code" will be true)
|| ([Zip Code]!=[Right_Zip Code] && [NOT_Zip Code]))
//Distribution Code test
(//Distribution code = right Distribution code AND the requirement to not have the Distribution code match is false
([Distribution Code]=[Right_Distribution Code] && ![NOT_Distribution Code])
// Distribution codes don't match and we want that ("Not_Distribution code" will be true)
|| ([Distribution Code]!=[Right_Distribution Code] && [NOT_Distribution Code]))
//Production Facility Code test
(//Production Facility code = right Production Facility code AND the requirement to not have the Production Facility code match is false
([Production Facility Code]=[Right_Production Facility Code] && ![NOT_Production Facility Code])
// Production Facility codes don't match and we want that ("Not_Production Facility code" will be true)
|| ([Production Facility Code]!=[Right_Production Facility Code] && [NOT_Production Facility Code]))
it's basically looking at two conditions to be met for each nested field (zips match AND they are supposed to OR zips don't match and they are supposed to not match)
Thank you for the solution. Somehow I am not able to open the workflow. It would be great if you can re-share the file. I have also found a simple solution of it. I basically combined two files with many to many relationship and then compared the criteria and assigned mark whether the record matches the criteria. I have attached the workflow here for your reference and your review of the solution. Thanks.