Data Structure Question

My app tracks expenses for a client. A Customer Code is assigned to each customer. A Job is a grouping of activities and expenses that includes one or more Customer Codes.

Within a Job, a staff member may visit 3 customers. The full cost of a $1,000 airline ticket must be allocated across the three clients.

From a Job, a Customer Code may be attached to the Job. A Customer Code is selected from a drop-down list and a numeric entry is made in a field that assigns an Expense Allocation Percentage. This can be repeated until the proper number of Customer Codes (and expense allocation percentages) are attached to the Job.

Records for Customer Codes and Expense Allocation Percentages are related to a Job. There is an Expense/Allocation record ID and Job ID for each of those entries.

Example:
Job1
Customer Codes Expense Allocation Percentage
ABC111 25%
ABC222 30%
ABC333 45%

Here’s my challenge. For each travel-related expense entry, the Expense Allocation Percentages must be applied to accurately show each customer’s share of the expense. The Expense records and Expense/Allocation records are two separate entities.

My question is how to organize the Expenses worksheet to both house the $1,000 airfare expense and to assign the proper amounts to the Customer Codes assigned to Job 1? I tried to use Array columns for both Customer1, Customer2, etc. and Allocation1, Allocation2, etc. but couldn’t figure out how to use the values in the array columns to achieve this.

Thoughts anyone?

I’m a visual person, so I may need some screenshots to understand how you have everything set up so far. So can there be multiple expenses for a job that are allocated to each customer, or is it only one expense? I think you have things set up pretty close to how I would do it. I’m not sure about using the array columns. I think this would be better handled in separate rows. My initial thought would be to have a sheet with all expenses for a job. Then the job sheet would have a relation to those expenses and a rollup to bring back the total expenses for that job. Then you would have a sheet with the job id and customer id that would have a relation to the job sheet and pull back the value of total expenses from the rollup. Then you could apply some math to calculate a total for each customer based on the allocation percentage.

1 Like

Thanks Jeff. I think I see where you are going. Will work on that.

You asked if there can be multiple expenses for a job that are allocated to each customer. Yes there can. So, during the course of a job, there could be travel costs associated with: lodging, airfare, rental cars, etc.

Presently there are four styles of expenses being capture in a single Expenses sheet. The Travel-related Expenses are the only records where the allocation routine is needed. The other three categories (I call them direct (versus allocated) expenses) simply assign each entry to a single customer code.

You recommended having an Expense Sheet for each Job. I’m not sure how that would work, as this would require the generation of an Expense Sheet anytime a new Job is created. Presently, all expenses (for all jobs and types of expenses) reside in the single Expenses sheet.

The goal is to be able to report both the direct and allocated expenses for every job. I believe this will require a separate sheet using IMPORTRANGE and Query functions so the back-end accounting team can view multiple jobs entries.

Thanks again for your help. I think Glide is one of the coolest technologies ever. I am not a technical person, but have been able to put some sophisticated functions together by watching videos and help from this community.

1 Like