Help

Re: Removing certain people from a calculation

773 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Dale
4 - Data Explorer
4 - Data Explorer

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

6 Replies 6

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:

  1. Jobs - Containing information on the jobs taken such as the load gross, person assigned, and the date of the job
    Screenshot 2022-05-16 at 11.42.23 PM
  2. People - Containing information on the people in your company, including how much % they get of each load gross
    Screenshot 2022-05-16 at 11.42.27 PM
  3. Weeks - Links all the jobs in a particular week - year together. (I included this just because it seems like something you might find useful)
    Screenshot 2022-05-16 at 11.42.33 PM

And so the workflow would be:

  1. You set the % of the load gross each person gets in the People table
  2. You create records in the Jobs table with all the information mentioned above
  3. Every week you go into the Weeks 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!

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?

Lot of this stuff seems redundant

momentsgoneby80
7 - App Architect
7 - App Architect

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.

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.

@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.