Question about forumla contains or containstext

1

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?

coda contains 2
coda contains 21058Ă—683 32.2 KB

coda contains 1
coda contains 1990Ă—665 30.1 KB

2

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)
image

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 !

image
image1072Ă—431 35.9 KB

Then, we make some arrangement to convert -1 to 0 and the positive number to 1, boolean power !

image
image1065Ă—401 36 KB

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

image
image1078Ă—704 52.8 KB

And then, just conclude by filtering the vendor brand you want :slight_smile:

image
image1396Ă—500 62.8 KB

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 !!!

image
image1295Ă—522 57.4 KB

I hope it can fit with your needs :wink:

Quentin

1 Like
3

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

4

Hi @yscias

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

image
image1364Ă—796 60.6 KB

Then you can use the same end of process !

Quentin

1 Like
5

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!

6

No problem @yscias

Actually if you read the doc of ContainsText, I just apply the explanation

image
image854Ă—443 75.3 KB

  • Where do you look for the brand > in the Name in Payable database
  • What do look ? The brand, in Vendor Table

Quentin

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.