Sep 08, 2024 11:28 PM
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" ])
Solved! Go to Solution.
Sep 09, 2024 04:05 PM
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
Sep 09, 2024 02:21 AM
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
Sep 09, 2024 11:17 AM
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
Sep 09, 2024 04:05 PM
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
Sep 09, 2024 11:55 PM
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
Sep 10, 2024 09:59 AM
Hi Pascal,
I did the following for the City table. I also had a Category table that simply listed each category.