Hello community I have been trying to solve an issue. I am looking for the correct formula to automatically fill the columns, (CCC Loads, variable expenses, fixed expenses) in the cost per mile calculator listed below. Those entries will be pull from 2 other table pictured below as well title (operating expenses & ccc loads). Please let me if anyone has any input on how I may be able to solve this issue.
How to automated functions
+4
+14Can you repost this with the images embedded? It looks like you have multiple separate Bases here, which means you will probably have to sync Tables from one Base to the other. Then use an automation to identify and link the records you need. It's too hard to say without a better formatted question though.
+4Can you repost this with the images embedded? It looks like you have multiple separate Bases here, which means you will probably have to sync Tables from one Base to the other. Then use an automation to identify and link the records you need. It's too hard to say without a better formatted question though.
+4
If I am correct I believe I do have my expenses and loads tables link to the cost per mile calculator. Hopefully these images are easier for you to view.
+4Can you repost this with the images embedded? It looks like you have multiple separate Bases here, which means you will probably have to sync Tables from one Base to the other. Then use an automation to identify and link the records you need. It's too hard to say without a better formatted question though.
Yes, I believe you understand exactly what I am trying to do. Sorry if my questions were not worded the best. I have stalled out in the process of implementing the automations.
+14The first thing you should address is the primary field in your Loads table. The primary field should be an identifier that is different from all other records, rather than just the date it was booked. You can use a formula field where you use string concatenation to create a Name field out of other fields. e.g.
{Date Booked}&" - "&{Origin and Destination}I would do something similar for the primary field in the Operating Expenses table too.
Then, the key to what you are trying to do is the formula you are using for Load Month in the Loads table. You need to get that correct (there is trailing "r" for some reason), and then you need to use the same formula in a field in the Operating Expenses table.
Then in your CPM Calc table you should create a "First of the Month" Date field. Then change the primary field in the CPM Calc table to the same formula that you used in the Load Month table (changing the date it is calculated from to the "First of the Month" field you just created.
So now both your Loads and Expenses table have a Month field that is similar to the primary field in the CPM Calc. Now you can use an automation that triggers when a record is created in the synced CCC Loads table, use the Update Record action type. Update the created record by setting the CPM Calc field to be the Load Month field of the same record. That should do it. Make a similar automation for the synced Expenses table.
+4The first thing you should address is the primary field in your Loads table. The primary field should be an identifier that is different from all other records, rather than just the date it was booked. You can use a formula field where you use string concatenation to create a Name field out of other fields. e.g.
{Date Booked}&" - "&{Origin and Destination}I would do something similar for the primary field in the Operating Expenses table too.
Then, the key to what you are trying to do is the formula you are using for Load Month in the Loads table. You need to get that correct (there is trailing "r" for some reason), and then you need to use the same formula in a field in the Operating Expenses table.
Then in your CPM Calc table you should create a "First of the Month" Date field. Then change the primary field in the CPM Calc table to the same formula that you used in the Load Month table (changing the date it is calculated from to the "First of the Month" field you just created.
So now both your Loads and Expenses table have a Month field that is similar to the primary field in the CPM Calc. Now you can use an automation that triggers when a record is created in the synced CCC Loads table, use the Update Record action type. Update the created record by setting the CPM Calc field to be the Load Month field of the same record. That should do it. Make a similar automation for the synced Expenses table.
Thanks I am definitely making progress. Any idea how to make this field more smooth looking? I type the formula as you said 
+14DATESTR({Date Booked})&" - "&{Origin and Destination}Will remove time component. Or you can use the DATETIME_FORMAT() function where you can specify how it should appear:
DATETIME_FORMAT({Date Booked},'MM-DD-YY')&" - "&{Origin and Destination}Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.