Skip to main content
Solved

UPDATED Linked Record and Look Up - Define value from list for a row

  • February 18, 2021
  • 4 replies
  • 38 views
T

Hello,
I am sure this is a pretty basic question but because I lack of knowledge I want to ask you what I am looking for in technical terms. How do I describe technically correct what I am looking for?
This is what I am looking for:

  1. I have a “list of zip codes” where each zip code is assigned to a certain value. This value is a tax rate.
  2. I also have a “database of clients” where there is always a zip code. I want to find out which tax rate applies to them according on the “list of zip codes”.
    3.I have a already a field where I link from my “database of clients” to the “list of zip codes”. If I type in manually in each line the zip code it works!
    BUT:

I created a table where I put the list of zip codes and tax values, I linked it from my table where I store my user data and now I can look up manually by typing in the zip code in my user table the tax value from the new table.

But I want that “automatically” so that the zip code from the user table automatically is requested in the other table.
How do I do that?

Thanks in advance!!

Best answer by augmented

I see, you’re looking for something completely hands off because your client table is getting populated by an external tool. You don’t want to have to go back and touch it to get the tax rate populated.

A couple of questions…1) can your tool not put the zip right in a linked field (to zip code table) in the client table? 2) do you mind having a linked field (to zip code table) and a tax rate lookup field (through the zip code link) in the client table.

For 2) You could use an automation just as you were trying to do, except I would try it slightly differently. In the update record action, select the zip code link field as the field to update, and then set the value to the zip code field that your tool populated. Then, the tax rate lookup field should reflect the tax rate.

Does that make sense?

4 replies

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • February 18, 2021

Hi Tim. Let me see if I understand. You have a client table with a zip code field (already populated) and a linked field to the zip code table that contains tax rates? And you want the tax rate in the client table via a lookup field through the zip code table link field?

If that’s true, AND the zip codes in your zip code field (not linked field) in your client table look exactly like the zip codes in your zip code table, then you could safely copy/paste from that column into the zip code table linked field column (that you want automagically populated). If they are not exactly the same, then you’ll create a bunch of new rows in your zip code tax rate table (but you could filter and eliminate those in the worst case)

Pasting the zip codes (text field I assume) into the linked field turns them into links to the zip code table via space magic (well, not really).

Let me know if I misunderstood. Cheers!

T
T
  • Tim_Sander
  • Author
  • Participating Frequently
  • February 18, 2021

Hi Tim. Let me see if I understand. You have a client table with a zip code field (already populated) and a linked field to the zip code table that contains tax rates? And you want the tax rate in the client table via a lookup field through the zip code table link field?

If that’s true, AND the zip codes in your zip code field (not linked field) in your client table look exactly like the zip codes in your zip code table, then you could safely copy/paste from that column into the zip code table linked field column (that you want automagically populated). If they are not exactly the same, then you’ll create a bunch of new rows in your zip code tax rate table (but you could filter and eliminate those in the worst case)

Pasting the zip codes (text field I assume) into the linked field turns them into links to the zip code table via space magic (well, not really).

Let me know if I misunderstood. Cheers!


Hi!
Thank you!

Via Copy Paste it already works!

My problem here is that I would like to have it “automatically”

Client table:
Name, Zip Code (populated), Tax Rate (linked field)

Zip Code table:
Zip Code (list), tax rate (List)

My problem is that the tool that populates the client list with zip codes, does not allow to transfer the zip code to a field that has a function like a linked field.
So I need in the client table to have a “normal” text field for the zip code that is automatically populated (by my tool) and then something that “reads” that zip code in that text field, checks in the Zip Code table for the tax rate ( when zip code from client list equals zip code from zip code table) and then updates the record in the client list with the correct tax rate according to the clients zip code.

In a nutshell:
I need a workaround since I cannot populate the zip code in a field that is linked.

I also tried now automations
Trigger:
Record is created
then
Find record in Zip Code table
where zip code = zip code from client table
update record in client table
take tax rate from zip code table to tax rate in client list table.

But I receive errors with no explananation.

Do you have any idea!

Thanks so much!

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • Answer
  • February 18, 2021

I see, you’re looking for something completely hands off because your client table is getting populated by an external tool. You don’t want to have to go back and touch it to get the tax rate populated.

A couple of questions…1) can your tool not put the zip right in a linked field (to zip code table) in the client table? 2) do you mind having a linked field (to zip code table) and a tax rate lookup field (through the zip code link) in the client table.

For 2) You could use an automation just as you were trying to do, except I would try it slightly differently. In the update record action, select the zip code link field as the field to update, and then set the value to the zip code field that your tool populated. Then, the tax rate lookup field should reflect the tax rate.

Does that make sense?

T
T
  • Tim_Sander
  • Author
  • Participating Frequently
  • February 18, 2021

I see, you’re looking for something completely hands off because your client table is getting populated by an external tool. You don’t want to have to go back and touch it to get the tax rate populated.

A couple of questions…1) can your tool not put the zip right in a linked field (to zip code table) in the client table? 2) do you mind having a linked field (to zip code table) and a tax rate lookup field (through the zip code link) in the client table.

For 2) You could use an automation just as you were trying to do, except I would try it slightly differently. In the update record action, select the zip code link field as the field to update, and then set the value to the zip code field that your tool populated. Then, the tax rate lookup field should reflect the tax rate.

Does that make sense?


I AM SO GRATEFUL! The combination of linked field + look up + Automation (Update record) works perfect! THANK YOU, AMAZING!!!

Terms & ConditionsAccessibility statement