Question about forumla contains or containstext

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?

Hello @yscias

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 :slight_smile:

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 :slight_smile:

Assuming that

  • 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 :wink:


1 Like

Wow you’re amazing! I will try this… but any idea, why doesnt the contain formula work?

Hi @yscias

Here is the way to create the column “LookForVendor” with ContainsText :

Then you can use the same end of process !


1 Like

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.