Return values by matching tables

1

Hello, sorry for the confusing topic name, but let me explain what I’m trying to do:

  • I have a table for task tracking which contains the columns “Client”, “Invoice Date” and “Expected Pay”.
    image

  • I have another table with the columns “Client name” and “Days for payment”.
    zYONm6xRiG

  • On the “Expected pay” column, I want to do the following:

  1. Check the “Client” column and compare it to the “Client name” column on the “Clients table”
  2. If the name matches, sum the amount on the “Days for payment” column to the “Invoice Date” to calculate the expected day for payment.

So, an example would be:
If “Client 1” takes 30 days to pay (which I manually add to the “Days for payment” column) and I sent the invoice on January 2nd, the “Expected pay” column should automatically return the “February 2nd” value.

My current solution is using an “if” formula for all clients. It looks like this:

image

The problem is that each time I register a new client, I have to add them manually to the formula.

Let me know if you need more information!

2

Hey! You can try something a bit simpler like this:
(Clients.filter(Client name.contains(thisRow.Client)).days for payment) + thisRow.Invoice Date

This should get you the expected pay date from that client based on the number in the Days for payment column in your Clients table.

3

Hi Nicole, thanks a lot for taking the time to answer!
I applied your formula but unfortunately got the “Could not calculate value” error.
Please take a look at it below:
image

Please note that I changed the name of the column on the Clients table to “DP” because there was already another column with the name “Days for payment”.

4

Hi @Nicolas_Sales, as you can see in the DT chip, it is a list of numbers, which you can’t directly sum to a date. It is a list because when you use the filter formula looking for a client name there could be more than one match.

If you are sure there will only be one match in your client table this is an easy way to fix the formula:

Days(Clients.filter(Client name.contains(thisRow.Client)).days for payment.first()) + thisRow.Invoice Date

As you can see I added a first() formula after the filter, in this way you convert the list into a single number. Just in case I also converted that number into a Dyson with the days() formula. That might not be necessary, but I’m on my phone and can’t check.

But actually, it’s much simpler than that. The clients column in your first table seems to be a relation column linked to the clients table. Then the formula should be as easy as: Invoice date + Clients.Days of payment.days()

5

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