Calculations in repeating fields not updating with each new line

[djeans]

I'm not sure i'm posting this in the right forum, but hopefully someone can help me out.

 

Short description of what I'm trying to achieve:

I'm in a band, the paychecks come to me, and I divide up the pay and give to each band member. It's on me to keep a record of the amounts so that I can 1099 them at the end of the year, and not be stuck with all the taxes. I'm trying to create a bare bones simple DB that I can enter each gig with a gross pay field, and then the name of each band member, and what they were paid.

 

The goal is for speed and efficiency, so my idea was to manually enter the date, venue name, and gross pay, then use a repeating field to enter the rest with calculations filling in as many blanks as possible.

 

I have a database with 2 related tables - Employees, and BandPay

Each band member has an Employee Number (i'm 1, Bassist, 2, drummer 3, etc) as well as the occasional additional musician that gigs with us. Tables are related by the EmpNumber.

 

My form is set up with repeating fields for EmpNumber, Name, and net pay - 

 

I enter a number in the # field, the corresponding name is looked up in the NAME field (that's working) and I would like the net pay to auto enter as a calculation of (gross pay / number of fields that are entered). (sometimes we play as a trio, sometimes quartet, etc . . ) - That is the part that is not working.

 

 

Essentially, I'd like the Net Pay field to auto update every time another name is added to the list, for each instance of the repeating field. 

 

ie, if the gross pay was $300 and only one name was in the repeating field, they would show as a Net Pay of $300. As soon as a second name is entered, both would now show $150, and so on.

 

Of course, after I figure that out, I'll try to create a report to show how much each person was paid over the year, but first I have to tackle this problem. 

 

Hopefully someone can help, or if you have a more elegant way of achieving this, I'm all ears.

 

Thanks in advance.

 

PS, I'm using an old version of Filemaker Pro (Version 10 Advanced)

 

I tried to attach my file, but I am not allowed.

 

[comment]

The short answer is that repeating fields are not the appropriate tool to use here. What you have is a many-to-many relationship between Payments and Employees, and this needs to be resolved by using a third table of PaymentAllocations to join them. In this table, there would be a separate record for each sum allocated to an employee out of a total payment received. Without this, you will have no way "to create a report to show how much each person was paid over the year".

 

The other issue is the automatic adjustment of the allocated amounts as you enter more employees that share the same payment. This is only possible if the allocated amount is left as an unstored calculation. Which could get slower over time as the number of payments increases. You might prefer a scripted approach, where you enter the participating employees first (using a portal to the join table placed on a layout of Payments), then run a script that divides the payment and writes the allocated amounts (as stored data) to a field in the join table.

[djeans]

Thank you for your response. 

 

I think I understand what you said, but just to clarify. . . 

 

I have created the PamentAllocations table with the fields: EmpNumber, Name, Date, and Payment.

 

Do I relate this table to both of the other tables by EmpNumber?

 

I assume I will need a few more fields on the BandPay table - one for each band member, and a corresponding pay field as well, yes?

 

I like the script idea, I'll try to work out how to make that happen.

[comment]

Do I relate this table to both of the other tables by EmpNumber?

 

You need to relate the new table to the existing two tables by the unique ID of a record in each of the two tables. I guess that's EmpNumber for Employees and ... PaymentID? or GigID? for the table where the received payment is recorded (and which I called Payments).

 

I assume I will need a few more fields on the BandPay table - one for each band member

 

No, you do not need - or want - a field for each band member. That would go against the very idea of a join table, which holds an individual record for each allocated amount.  You only need fields for:

 

• EmpNumber (links to the Employees table);

• PaymentID (links to the Payments table);

• AllocatedAmount (the actual amount due to the related employee out of the related payment).

 

In addition, an AllocationID field, auto-entering a serial-number, would be good practice and might become useful at some later stage.

[djeans]

 

No, you do not need - or want - a field for each band member. That would go against the very idea of a join table, which holds an individual record for each allocated amount.  You only need fields for:

 

• EmpNumber (links to the Employees table);

• PaymentID (links to the Payments table);

• AllocatedAmount (the actual amount due to the related employee out of the related payment).

 

I'm confused now. Are these fields on the Allocation table or the payments table. (maybe we should rename that one to Gig table for clarity?)

 

On the payment/gig table, the unique id would be an auto-generated serial #, yes? 

And there would be an auto generated Serial on the allocation table too?

 

I guess I don't understand the allocation table concept.

 

If you're willing, here is a link to the original file I started, and a new one I just tried to start based around what I thought You meant. Care to open it and make the links for me? I really appreciate the help.

 

https://www.dropbox.com/s/cyxe47xmxez4x4t/FilemakerExamples.zip?dl=0

[comment]

See if this makes it any clearer:

 

PaymentAllocations.fp7.zip

 

Note that we are assuming there's only one payment for each gig.

[Lee Smith]

If you're willing, here is a link to the original file I started, and a new one I just tried to start based around what I thought You meant. Care to open it and make the links for me? I really appreciate the help.

 

https://www.dropbox.com/s/cyxe47xmxez4x4t/FilemakerExamples.zip?dl=0

Please post your file here. If you don’t know how to do that, then just follow these steps posted here. ATTACH FILE

[Rick Whitelaw]

What if a band member leaves and is replaced? I manage a large system that pays musicians. I suggest you have an Engagements table. Each record in that table represents a gig. The gig can be any length, a night, a week, a month. Then have a "Band" table that records who is on the gig. This is one to many Engagement to Band members. Then have a Fees table. Each Fee is related to one musician and of course one musician can, over time have many Fees.Of course, keep a Contacts table going too. This would be related to Band Members. That's a start. FileMaker can, with a bit of work, calculate and print your 1099 forms as well. It all depends on whether your needs will grow and how much history you wish to maintain. This is probably overkill for your needs, but who knows?

[djeans]

See if this makes it any clearer:

 

PaymentAllocations.fp7.zip

 

Note that we are assuming there's only one payment for each gig.

Thank You!  It looks like that functions pretty much exactly as I was imagining. Now just have to figure out the final reporting to break down how much each was paid.

What if a band member leaves and is replaced? I manage a large system that pays musicians. I suggest you have an Engagements table. Each record in that table represents a gig. The gig can be any length, a night, a week, a month. Then have a "Band" table that records who is on the gig. This is one to many Engagement to Band members. Then have a Fees table. Each Fee is related to one musician and of course one musician can, over time have many Fees.Of course, keep a Contacts table going too. This would be related to Band Members. That's a start. FileMaker can, with a bit of work, calculate and print your 1099 forms as well. It all depends on whether your needs will grow and how much history you wish to maintain. This is probably overkill for your needs, but who knows?

That sounds amazing. Especially the calculating and printing the 1099s. I haven't played around with Filemaker in a few years (notice the old version lol) and I'm a little rusty on how to get things accomplished, but I'd love  to try to implement some or all of your ideas.

[comment]

Now just have to figure out the final reporting to break down how much each was paid.

 

Actually, this part becomes trivial once you have the correct structure in place. Base you report on the PaymentAllocations table. Find the records in the year of interest, sort them by EmpID, and use a sub-summary part by EmpID to show the subtotals.

[comment]

Then have a "Band" table that records who is on the gig. This is one to many Engagement to Band members.

 

I didn't get this part. It seems you're saying that Band is the join table between Engagements (here: Gigs) and Contacts (here: Employees). If so, why is there a need for the Fees table? Unless you are plan on recording multiple fees per a single Engagement/Contact combination?

[djeans]

Actually, this part becomes trivial once you have the correct structure in place. Base you report on the PaymentAllocations table. Find the records in the year of interest, sort them by EmpID, and use a sub-summary part by EmpID to show the subtotals.

When creating the subsummary report, I'm running into a couple of problems. 

 

I'm only getting the first 2 EmpNumbers on the report, and only the amount of the last payment allocation, not the sum of all of them.

 

What I did:

Create a new layout, with a header (for the labels) and a "Sub-summary when sorted by" part with PaymentAllocations::EmpID selected, with print below chosen.

Then I placed the fields PaymentAllocations::EmpID, Employees::Name, and a new summary field that I created called PaymentAllocations::TotalPay that is a Total of cAmount, with running total and restart summary for each group sorted selected, and set to summarize repetitions all together.

 

I also tried just placing PaymentAllocations::cAmount and PaymentAllocations::sTotalAmount as well, but neither of them worked. 

[comment]

I said this was simple, didn't I? So why are you making it so complicated?

 

 

PaymentAllocations.fp7.zip

 

Note: you can delete the Body part from the Report layout, leaving only summary values.

[Rick Whitelaw]

I didn't get this part. It seems you're saying that Band is the join table between Engagements (here: Gigs) and Contacts (here: Employees). If so, why is there a need for the Fees table? Unless you are plan on recording multiple fees per a single Engagement/Contact combination?

There is no join table involved in this idea. Sorry I'm not more specific.

[comment]

There is no join table involved in this idea.

 

Well, then I as i said, I don't get this.

[Rick Whitelaw]

Sorry Michael. I was not at a computer then and I'm not at the moment. I suspect you're correct. I see now my needs are every different from the op's. I have multiple orchestras and engagements happening at the same time. I call my Fees table "Fee_Engine" because it mostly calculates. And it's, in my situation, necessary. My original advice was far more complicated than needed. The op's situation in terms of musicians hired likely doesn't change that much, but . . .

[djeans]

Sorry Michael. I was not at a computer then and I'm not at the moment. I suspect you're correct. I see now my needs are every different from the op's. I have multiple orchestras and engagements happening at the same time. I call my Fees table "Fee_Engine" because it mostly calculates. And it's, in my situation, necessary. My original advice was far more complicated than needed. The op's situation in terms of musicians hired likely doesn't change that much, but . . .

Correct. It's mostly the same 4 people, with the occasional sub musician if someone is sick or on vacation, etc.

 

It's also, for the most part, the same venue week to week, with the occasional added show now and then. For my needs currently, this simple solution should work for keeping track of how much is paid to who for the purpose of then later creating 1099 forms. If FMP can do that too, that is pretty cool, but since I couldn't figure out how to create this simple report, I doubt I'll figure that one out either.

[djeans]

Thanks again for all the help. It looks like it will work perfectly for my needs, and hopefully I'll be able to expand it when and if my needs expand.

 

On a whim, I created a duplicate sub-summary report and placed a graphic in the body of a scan of a 1099 form and placed the appropriate fields where they would go. I also added more fields to the employee table (address, etc) and placed those fields on the form as well. This is probably a ghetto way of creating a 1099, but to my surprise, when I ran the report, it generated a 1099 for each band member. I have no idea if this is a "legit" way of creating 1099s that I can give the guys, but I still thought it was pretty cool!

[djeans]

1 more question. How would I constrain the found set of the sub-summary records? I'm not sure that made sense, but basically, I don't need to generate 1099s for anyone I paid less than $600 to. So, My script currently creates all 4 versions of 1099s for everyone I paid out to, regardless of amount. I'd like it to exclude anyone under $600. I tried adding a constrain found set line in the script, but it is looking at individual records, not the sum, which makes sense, but I'm sure there must be a work-around. Is this possible?

[comment]

I tried adding a constrain found set line in the script, but it is looking at individual records, not the sum, which makes sense, but I'm sure there must be a work-around. Is this possible?

 

It's possible, but not simple. As you have noted, you cannot base a find on the future results (summary values) of the (yet unperformed) find. You must loop through the found set and omit every record whose group's sub-summary value is less than the limit.

 

The neat way to do this is by using a technique known as "FastSummaries" by Mikhail Edoshin - but you can also do it the pedestrian way, just by looping through the found set record-by-record and omitting any record where =

GetSummary ( PaymentAllocations::sTotalAmount ; PaymentAllocations::EmpID ) < 600