May 16, 2022 08:19 AM
So im building a base for my trucking company. I am the owner as well as driver, I also have three owner/operators who are paid by percentage of the load gross. Im stumped on how to take myself out of those calculations - I need to track the money that I make but there are no deductions or percentages taken out of my money as im the boss :slightly_smiling_face: Also how to make a deduction automatic but it would come out of the weekly settlement for the driver, not each load.
Thanks - Dale Clarke
May 16, 2022 08:41 AM
Hi Dale, check this out. To view the formulas / automations, duplicate the base by clicking the title of the base at the top of the screen and then click the three horizontal dots on the right
The idea is we’ll make three tables:
Jobs
- Containing information on the jobs taken such as the load gross, person assigned, and the date of the jobPeople
- Containing information on the people in your company, including how much % they get of each load grossWeeks
- Links all the jobs in a particular week - year together. (I included this just because it seems like something you might find useful)And so the workflow would be:
People
tableJobs
table with all the information mentioned aboveWeeks
table to view the weekly summary.With reference to point 3, you’re either going to have to paste the Week - Year
value into the Weeks - Link
field every time you create a new Job, or use the automation that I’ve set up in the base linked above.
If you do end up using the automation I set up, I’d recommend you create job records via a form. This is because the automation is triggered when a new record is created, and so might not run properly if you don’t create job records via a form.
You can probably end up doing some cool stuff with the Weeks table, including sending out email invoices and such with the summarized information. Let me know what you think!
May 16, 2022 09:00 AM
Not entirely certain I understand how to do the job records form adam_c, also ive got the people all set up already and the calculations in process in a linked table but im thinking I need to make that field a roll up with a formula to separate me out of the calculation?
May 16, 2022 09:47 AM
Lot of this stuff seems redundant
May 16, 2022 12:21 PM
Hi @Dale!
@Adam_TheTimeSavingCo has supplied a full solution with lots of room to expand.
To answer just the question of how you remove yourself from a calculation or use a different percentage for yourself, without adding other fields, you simply use conditional formula in a formula field. If the other owner/operators have diffent percentages from each other then you nest formulas within each other.
IF({Field with user name}='Dale Clarke',
'Do this',
'Otherwise do that'
)
“Do this” or “do that” can be a formula, text or emoji.
Nested it would look something like this
IF({Field with user name}='Dale Clarke',
'Do this',
IF({Field with user name}='Operator 1',
'Operator 1 action or text',
IF({Field with user name}='Operator 2',
'Operator 2 action or text',
IF({Field with user name}='Operator 3',
'Operator 3 action or text',
'User not selected'
)
)
)
)
If you want it to do nothing if no user is selected then it would look like this
IF({Field with user name}='Dale Clarke',
'Do this',
IF({Field with user name}='Operator 1',
'Operator 1 action or text',
IF({Field with user name}='Operator 2',
'Operator 2 action or text',
IF({Field with user name}='Operator 3',
'Operator 3 action or text'
)
)
)
)
How to apply the deduction to the weekly settlement and not each load depends on what the rest of your base looks like.
May 16, 2022 10:11 PM
Ah hm, apologies, I didn’t know you already had stuff set up!
From what I understand, you aren’t able to remove specific records via a formula in a rollup field, and so you should try @momentsgoneby80 's solution to customize the output of the field that contains the payment information, and then roll up on that.
May 17, 2022 09:31 AM
@Adam_C
As I’m sure you know; you can in the rollup, in the conditions part, tell it to only roll up when certain names or collaborators are selected/typed in.
I’ve also recently learned from an answer by @Kamille_Parks (I’ve forgotten in what thread) with link to here that you can type pretty much any formula in a rollup formula field and that way sometimes avoid the need for a rollup and a formula. Only differences is that in the rollup fields formula box it won’t suggest field names or try to auto-compute what you’re typing. I’ve only tried it a few times, but for those cases it worked great. Could maybe be of use here, but depends on what the rest of the base looks like i assume.