Im trying to get a column to return a certain value if the row contains a value from another table, but it doesnt seem to work. What am i doing wrong?
I’m not a huge fan of
Contains formula, then I always try to do stuff without it.
Not sure this is the easiest nor the most generic solution, but let’s go
I added the automatic rowid to your VendorTable, but that can work with any unique key you want (in my case a number)
First step, I would run through vendor table using
ForEach(), to check if I
Find() the current value of Vendor Name INTO Payable table (and not the opposite !). You can see that if you have a positive number, it’s linked to the vendor you’re looking for, a -1 si that you found nothing. You can see there that we’ve already found in which row of vendor table the good brand is !
Then, we make some arrangement to convert -1 to 0 and the positive number to 1, boolean power !
OK, now that you know the position of your good vendor name, let’s make a sumproduct to convert this position to the row id of the vendor you want
And then, just conclude by filtering the vendor brand you want
- you vendor table name are unique,
- you have only one brand in your payable name
- the ortograph is OK in Payable name
you can do any stupid text including the brand, it will find it !!!
I hope it can fit with your needs
Wow you’re amazing! I will try this… but any idea, why doesnt the contain formula work?
Here is the way to create the column “LookForVendor” with
Then you can use the same end of process !
Thanks a lot! I dont really understand it, but I will slowly try to figure it out haha. You’ve been a massive help with many things!
No problem @yscias
Actually if you read the doc of ContainsText, I just apply the explanation
- Where do you look for the brand > in the Name in Payable database
- What do look ? The brand, in Vendor Table
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.