Trouble Distributing Costs

Hello All,

I'm stumped so I'm reaching out to the community for some help. Here
is an overview of what I am trying to do...

I have Two tables, one titled "Employee", the other titled "Cost
Center". So I have 25 employees that I have calculated the total
company expense to have any particular employee on the payroll. Then I
have the Cost Center table where there are 40 records that I am
attempting to distribute the costs of the employee to multiple records
in the Cost Centers table.

I can easily distribute the employee costs equally to any number of
cost centers.

Where I am having trouble is if I would like to have one employee set
up as (for example) 5% in one cost center, 80% in another, and 15% in
another, and have that percentage and calculated cost show up in a
portal in the Cost Center table. At the end of the day, the portal is
for visual only. I could get by without having a nice graphical
representation per Cost Center record.

I've tried the Case function, playing with portals and putting my head
in the freezer for an extended time. None of them worked.

I should point out that I am using a value list to define the records
in the Cost Center table. I use this same value list in the Employee
table to create the relationship between the two tables. Should I not
use a value list?

I'm using FMP 8.5 Advanced on a Mac OS X.4

Re: Trouble Distributing Costs

In article <1192517012.346700.271670@z24g2000prh.googlegroups.com>,
benross7@yahoo.com wrote:

> Hello All,
>
> I'm stumped so I'm reaching out to the community for some help. Here
> is an overview of what I am trying to do...
>
> I have Two tables, one titled "Employee", the other titled "Cost
> Center". So I have 25 employees that I have calculated the total
> company expense to have any particular employee on the payroll. Then I
> have the Cost Center table where there are 40 records that I am
> attempting to distribute the costs of the employee to multiple records
> in the Cost Centers table.
>
> I can easily distribute the employee costs equally to any number of
> cost centers.
>
> Where I am having trouble is if I would like to have one employee set
> up as (for example) 5% in one cost center, 80% in another, and 15% in
> another, and have that percentage and calculated cost show up in a
> portal in the Cost Center table. At the end of the day, the portal is
> for visual only. I could get by without having a nice graphical
> representation per Cost Center record.
>
> I've tried the Case function, playing with portals and putting my head
> in the freezer for an extended time. None of them worked.
>
> I should point out that I am using a value list to define the records
> in the Cost Center table. I use this same value list in the Employee
> table to create the relationship between the two tables. Should I not
> use a value list?
>
> I'm using FMP 8.5 Advanced on a Mac OS X.4

What is normally done and what you need is another table: "Cost Center
Breakdown". This is an intermediary table which stores separate records
for each employee and each cost centre they're involved in. This table
would need a few fields:

Employee Name Text Used to link to/from Employee table

Cost Center Name Text Used to link to/from Cost Center table

Percentage Number Amount of Employees time spent on this
Cost Center


The cost for each employee can then be calculated by retrieving the
Expense and multiplying by this Cost Center's Percentage.
eg.

EmployeeCost Calculation, Number Result
= (rel_ToEmployee::Expense * Percentage) / 100

Notes: If you're using unique codes for the Employee and Cost Center
than you would use those as the link fields instead of the two Name
fields. You would still need the Employee Name and Cost Centre Name,
but they could be calculations that retrieve their data via the
relationships to the appropriate table.
eg.
Employee Name Calculation, Text Result
= rel_ToEmployee::Employee Name

Cost Center Name Calculation, Text Result
= rel_ToCost Center::Cost Center Name



You can then access this employee-cost center data from the Employee
table and / or Cost Center table.


In the Employee table you can have a relationship link to this new Cost
Center Breakdown table. This will allow you to have a portal on the
Employee table layout that shows all the Cost Centers this employee is
involved in.
eg. In the "Fred Flintstone" Employee record the portal might show:

Sleeping 45% $450
Driving 10% $100
Working 35% $350
Eating 5% $50
Bowling 5% $50

You could also create a new Calculation field in Employees table to
make sure you've got your Percentages correct. Put this field outside
the portal.
ie.
TotalPercent Calculation, Number Result
= Sum(rel_ToCostCenterBreakdown::Percentage)


In the Cost Center table you can have a relationship link to the new
Cost Center Breakdown table. This will allow you to have a portal on
the Cost Center layout that shows all the employees involved in this
Cost Center.
eg. In the "Bowling" Cost Center record the portal might show:

Barney Rubble 3% $25
Fred Flintstone 5% $50
Mr Slate 1% $5

You can have a Calculation field that totals the amount of money spent.
Put this field outside the portal.
eg.
TotalExpense Calculation, Number Result
= Sum(rel_ToCostCenterBreakdown::EmployeeCost)



For printed reports, you should use a Cost Center Breakdown table
layout and have Summary fields to display the totals - sorting /
summarising by either Employee or Cost Center.






Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)