Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Auto-linking records with conditionals

Topic Labels: Automations Data
Solved
Jump to Solution
357 5
cancel
Showing results for 
Search instead for 
Did you mean: 
dreamer
4 - Data Explorer
4 - Data Explorer

I am creating a table to get a detailed look at my finances. I want to see cost per day by city and category for the following table schema:

Purchases
name, cityName, cost, category

Cities
cityName, daysInCity


How do I create a table with the following fields:
cityName,category,costPerDay
city1,food,$20.00
city1,lodging,$100.00
city1,activities,$50.00
city2,food,$30.00
city2,lodging,$110.00
city2,activities,$60.00

I want this to be automated so minimal manual work besides adding each purchase as a record is unnecessary.

Currently, I am running into trouble auto-selecting the records by city and category. I have tried with automations but I am running into an unknown error:


Invalid filter on Category (Invalid filter value: [ "Essentials" ])

1 Solution

Accepted Solutions

Then, you could do the following.

Pascal_Gallais_0-1725922581807.png

"City" field in table "Purchase" is linked to table City, so that we can add some "count" and "rollup" fields:

Pascal_Gallais_1-1725922650184.png

Field Nb Days is a "count" field on table "purchase":

Pascal_Gallais_2-1725922708480.png

"Food total cost" is a rollup on value cost from table "purchase" where category is "food":

Pascal_Gallais_3-1725922789598.png

"Food cost per day " is a formula that divides these 2 values:

Pascal_Gallais_4-1725922840293.png

You can then replicate to get your cost per day column for each category.

This is not the direct answer to your question. If you really need to have a table with one row for each city and each category, it is possible via an automation to create a record any time a new record is inserted in table purchase.

Ask me if you want me to detail this solution.

Regards,

Pascal

See Solution in Thread

5 Replies 5

Hello,

I am not sure that creating another table is necessary to get your "cost per day" information:

Pascal_Gallais_0-1725873152690.png

Here is what I did.

-1- Table city

This table just lists the different cities:

Pascal_Gallais_1-1725873235758.png

-2- Table journey

This table lists the differents journeys and the number of days of each of them:

Pascal_Gallais_2-1725873314832.png

Field "City" is a linked field to table "city".

-3- Table purchase

Pascal_Gallais_3-1725873459331.png

Manual fields:

- Journey / linked field to table journey

- Category

- Cost

Lookup fields:

- City from table journey

- Nb Days from table journey

Formula

- Cost per day

Pascal_Gallais_4-1725873614614.png

Once your formula is defined, you will need to go on the "formatting" tab:

Pascal_Gallais_5-1725873670258.png

Regards,

Pascal

 

Thanks for the answer! I might have been unclear with the original question. Here is a clarified schema:

Purchases
date, name, cityName, cost, category

09/09/24, Dinner at Freddy's, New York, $100.00, Food

09/09/24, Lunch, New York, $20.00, Food

09/10/24, Bus, Boston, $50.00, Transportation

Cities
cityName, daysInCity

New York, 10

Boston, 15

Then, you could do the following.

Pascal_Gallais_0-1725922581807.png

"City" field in table "Purchase" is linked to table City, so that we can add some "count" and "rollup" fields:

Pascal_Gallais_1-1725922650184.png

Field Nb Days is a "count" field on table "purchase":

Pascal_Gallais_2-1725922708480.png

"Food total cost" is a rollup on value cost from table "purchase" where category is "food":

Pascal_Gallais_3-1725922789598.png

"Food cost per day " is a formula that divides these 2 values:

Pascal_Gallais_4-1725922840293.png

You can then replicate to get your cost per day column for each category.

This is not the direct answer to your question. If you really need to have a table with one row for each city and each category, it is possible via an automation to create a record any time a new record is inserted in table purchase.

Ask me if you want me to detail this solution.

Regards,

Pascal

Hello,

My apologies, there is a mistake in my solution. The number of days in table "city" is not correct since it counts the number of rows for a given city in the table purchase, meaning that if you have 2 rows for New York on the same day, it will count it twice.

You need to transform field "Nb Days" as such:

Pascal_Gallais_1-1725950760482.png

Further more, there are to ways to understand your need. Either you want to count all dates for a given city even if one or more of them are not active for the "computed category", or you only want to count active dates.

For example, let us consider these rows in table purchase:

Pascal_Gallais_2-1725951059200.png

There are 3 dates for New York, but only one active on category "Transportation". If you want to calculate cost per day only for active days, you need to define a "Nb field" for each category in table city:

Pascal_Gallais_3-1725951229614.png

You can then use this field in the formula "cost per day transportation".

Regards,

Pascal

 

Regards,

Pascal

 

Hi Pascal,

I did the following for the City table. I also had a Category table that simply listed each category.

Screenshot 2024-09-10 115628.png