How can I assign one expense between various business units?

[dhurtado2024]

Hey there! I am trying to allocate monthly expenses across our business units (BUs)  based on pre determined splitting criteria, some of those criteria change month over month, such as the percentage of revenue each business unit is producing. What I'm trying to do right now (but failed at it) is linking four different tables: 

1. Transfers --> All the payments we have done in a month

2. Suppliers --> A list of all suppliers we currently have

3. Splitting criteria --> A list of all the possible splitting criteria (Equal splitting across BUs , splitting by  revenue%, etc) 

4. Splitting % --> Given each splitting criteria, how would the percentages be assigned to each BU each month. For example if the split is by revenue %, what percentage of revenue each BU produced every month, or if it is equal splitting all BUs get 33% every month. This table has the following columns: month, splitting criteria, pctg to BU1, pctg to BU2, pctg to BU3. 

What I've been trying to do is: 

1. To each supplier in the Suppliers table (2) assign a splitting criteria from the splitting criteria table (3). This I done by adding a field and manually assigning the splitting criteria one by one 

2. To each bank transfer on the transfers table (1), which contains the supplier name, lookup the splitting criteria from the suppliers table (2) 

3. Given the splitting criteria, and the payment date, on the transfers table (1) I would bring from the splitting % table (4) the following columns: pctg to BU1, pctg to BU2, pctg to BU3. This is where I'm stuck, when I try to do a lookup to the splitting % table (4) it does not appear as a lookup option.

4. Create three new columns on the transfers table (1): 
* Total to BU1$ : pctg to BU1 x Transfer Amount 
* Total to BU2$: pctg to BU2 x Transfer Amount
* Total to BU3$: pctg to BU3 x Transfer Amount

This way by summing each of this three columns every month I know how much was spent per business unit. I would really appreciate any help

[Pascal_Gallais-]

Hello,

Someone may come up with a simpler solution, but this one seems to do the job.

-1- Table "Splitting criteria"

I added a field "Splitting code" with value A or B

-2- Table supplier

Linked to table "Splitting criteria" via the field "splitting type" and with a lookup to get the "splitting code"

-3- Table Monthly splitting

2 rows for each month with the primary field being "YYYYMM" with the code A for the first row and the code B for the second row

You then set values to 33,33 for all "A rows" and set the expected values for all "B rows"

-4- Table Transfer

Link it to table supplier and add 2 lookup fields to get the "splitting type" and "splitting code"

Add a formula to build the "Monthly splitting code":

DATETIME_FORMAT(Date,'YYYYMM') & {Splitting code}

Add a formula "ready for automation":

IF(LEN({Monthly splitting code})=7,'yes','no')

This is because we will create 2 automations to get the link with table "Monthly splitting", but we want to make sure that it does not run as long as all required information has not yet been provided.

Add a field "Monthly splitting link" linked to table "Monthly splitting" and 3 lookup fields for the % of each BU.

-5- Automation "create transfer"

This one deals with the case when a record is created into table transfer:

Trigger:

Action "Update record":

Once this automation is truned on, any new row in table transfer will be automatically linked to table "monthly splitting" and hence bring down the % per BU.

-6- Automation "Update transfer"

This one deals with the case of an existing row being modified in table transfer:

Trigger:

Add a "conditionnal logic action" with the same "Update action inside":

This condition is important to deal with the different cases:

- Supplier's splitting criteria is modified, automation runs

- Date is modified, automation runs

- Date or supplier fields values are deleted, automation does not run. But the automation "Create transfer" will run when the information is provided.

-7- Initial values

Automations will not run for all the rows that you created before defining the automations. The easiest way to initiate the link with table "montlhy splitting" is to copy paste the values of field "Montly splitting code" into field "Montly splitting link"

Regards

Pascal