Populate field based on match with another field in another table

App Development
1

I have two tables. Table 1 has License Number (unique), Company, and Employee. The Company field is empty and it may help to know this table is updated weekly. Table 2 has License Number (same as in Table 1) and Company. Note that a company can have multiple licenses.

I want to populate the Company field in Table 1 with the data in the Company field in Table 2 when and only when the License Number matches. I’ve tried using a connected field on Table 1, but it forces me to manually select the Company from a drop-down menu. I’m looking for an automated process. In a spreadsheet, I can accomplish this with the vlookup formula, so it makes me think there should be a way to do this in Tadabase?

2

The only way to achieve this would be with a Pipe. It’s not super complicated, but requires some Pipe knowledge.

You’d need to create a pipe that queries that second table and filters to only show records where license number matches then update the connection field with that ID.

You can see this thread of something different but similar concept: Count Users with specific status

3

A record utilities pipe?

4

It would need to be a custom built pipe, not the record utility pipe. The record utility pipe is some custom functions that we host behind the scenes (like duplicate records and child records).

5

I guess the example is not similar enough because I’m stuck. I built the pipe and changed the response data type from number to string. But it looks like this worked for the other user because he’s using a form to run the pipe. Do I need to use a form or is there another way to run the pipe? Again, I’m just trying to automatically pull data from one table to another based on a match condition so there’s no interaction with the user as in the other example.

6

Rick, can you open a support ticket with our team and I’ll try and assist. We’ll need a lot of details like which fields and tables you’re trying to do the “vlookup” for and I’ll try and guide you accordingly.

The way Pipes work is you can just retrieve the value and add the pipe response value inside the data source of a table. Essentially, you’re only displaying that data, not actually saving it to the first table. You can see a bit more here:

7

Thanks Moe, that sounds like what I want to do. I’ll read through the pipes documentation again to see if I can figure it out. And then I’ll open a ticket if I’m still stuck!