Auto-linking records with conditionals

[dreamer]

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" ])

[Pascal_Gallais-]

Hello,

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

Here is what I did.

-1- Table city

This table just lists the different cities:

-2- Table journey

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

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

-3- Table purchase

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

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

Regards,

Pascal

[dreamer]

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

[Pascal_Gallais- · Accepted solution]

Then, you could do the following.

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

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

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

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

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

[Pascal_Gallais-]

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:

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:

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:

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

Regards,

Pascal

Regards,

Pascal

[dreamer]

Hi Pascal,

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