Cumulative Calc Solution for Name & Date

[chlowden]

Hello

I am trying to figure out how to have a field that automatically calculates the cumulative salary of each contact in a very simple payroll db.

There are 30 contacts in 1 table. A 2nd Table contains the contact pay sheets. One of the fields in the pay sheet is a cumulative - running total of all that person's salaries in a calendar year. I am struggling to find the tools to tell the calc line to only sum using the contact ID number in the paysheet & only over a calendar year?

Many thanks

[mr_vodka]

You can either create a join table that stores the employeeID and year to join to the pay sheets, or you can try using a filtered relationship with a global field. I think we may need some additional info.

[chlowden]

I'm trying to get my head round this. If I setup "join field" that means I will have a new table that will have a date & an amount associated to a person. The sum of this table, defined by the year will make the, will be created by a calc back into the original file. Is it possible to go loop back into the same entry? What would the calc look like?

[Søren Dyhr]

Shouldn't it be said that the urge to do this is a two ways decision, depending on the scale ... aggregate as suggested doens't scale particular well.

Similar does the rendering of a summary field put somewhere in the same layout as the "mongering" of data takes place, a metaphor deviating slightly from the database realm, lending a lot from spreadsheets - not what this tool from a engineering point of view is set out to solve, but however often made as a flash in a frying pan by marketing people in abstraction anaemia!

Something which by the present version, requires a thorough consideration of when a field really is needed updated, and why. Similar should the measure of updating really be scrutinised in "Nice to knows and Need to knows" ...even though, plenty of processing power might be available.

Why are such consideration necessary? Because with loosely and abstract formulated lines of questioning, are nobody aware if the requirement should fit database mining for a supermarket or a mom'n'pop shop with the occasional sale of something.

You can either create a join table that stores the employeeID and year to join to the pay sheets, or you can try using a filtered relationship with a global field. I think we may need some additional info.

Why is straight forward sub summary reporting ignored here?

--sd

Edited April 27, 2009 by Guest

[chlowden]

Soren, Many thanks for the reply.

Sorry that I have not quite understood all the subtleties of the your 'carpal' thinking, but if I get one thing, it is why I don't use a sub summary report. Admittedly, we are in the Excel realm, but not exactly as this is the sum part of a db. A sub summary report is a powerful reporting over view. What I am looking for is the sum of figures defined by a sort and that it be done automatically. This is for a theatre company that does 60 pay slips a year, so no big deal. I figure that for a small db, doing a quick sort through the db to calc the field is possible. If it is a million payslips, another system is needed.

[Søren Dyhr]

Alright this might give you a clue:

http://fmforums.com/forum/showpost.php?post/266487/

...just remember the more "real estate" you dedicate to dependent rendering the slower it gets.

Admittedly, we are in the Excel realm

...and the reason for migration is?

--sd

[chlowden]

The clue is very interesting.

I say excel because often pay slips are created with it. The pay slip is just one of many things i do with FM

Many thanks

[chlowden]

Ok. I'm struggling. I have set up a new table that has a connection between the payslip & the new table. There is also a field for the year, the salary paid the ID of the person & a summary field. I am struggling with the case function to to resume it all. Can anyone explain in layman terms how to have numerous parameters in the case function?

I find the FM help is written in a language that I have trouble understanding. Actually is there an FM book for idiots like me that explains all this?

[Søren Dyhr]

I think you have another more crutial problem, because you have said the other table contains:

A 2nd Table contains the contact pay sheets

...where is the data in the paysheets stored? The sheets (if the metaphor makes sense) should be the holder of a series of transactions where each transaction should be broken out in lines each residing in their own records ... so my guess is that your are a table short in your original description already.

As it is, should reporting as such even if using Ugo's method, preferably be made on the most atomic of the tables.

Your lack of the join table, suggest you use repeating fields instead ... which unfortunately only solves a graphical problem:

http://databases.about.com/od/specificproducts/a/firstnormalform.htm

I have in an attempt to abide to your subsummary reporting refusing attitude, made you a template to show what actually can be accomplished via structure, and avoidance of maximum exploitation of condtional statements, since the relational structure provides filtering.

--sd

chlowden.zip

[chlowden]

Many, many thanks for all your work. I have had a look at it but I am still perplex. I have attached my work so far. But it will need explaining as it is in french.

This is a simple - work in progress - db to help my wife deal with her theatre company. The primary purpose is to create payslip for the actors & techs. For the moment, her only actor is called Lawrence Olivier ... hmmm.

User: admin

Password: admin

The opening page is a resume of the project: MACBETH with the representations booked, the 'PERSONNEL' who will work on it & the FACTURE (INVOICES) that have been issued (this does n't work yet).

In the layouts is CREE FICHE DE PAIE ARTISTE (Create Artiste Payslip). This is a layout that the user uses to fill in the payslip form & it calculates all the very high french taxes. There is a big button "CREE FICHE DE PAIE" that runs a script that will copy all the fields into a new layout called "ARCHIVE FICHE PAIE ARTISTE" (Archive Artiste Payslip).

Now I am sure that you alarm bells have already sounded, but there is a simple reason why I have such a complex copy script. I found no other way to have a 'hardcopy' version within the db that I was sure could not be easily modified. Once the payslip is created, it becomes its own entity & has no subsequent connection with any previous action i.e. if the actor changes house address, I don't want all his old payslips changing address.

So ... at the bottom of the newly created ARCHIVE FICHE PAIE ARTISTE is a box called CUMUL (CUMULATE) which I am trying to make work (among other things). The idea is that the field NET IMPOSABLE (TAXABLE INCOME) should be the sum of all that persons salaries in any calendar year.

Following what I understood of your instructions, I created a new table called ARTISTE_CUMUL with a layout that sucks in info from the created payslip. From here, I have tried the sum functions & copied your model but with little success. What bothers me is that, logically, I don't see why it is so difficult (apart from the fact that I really don't know what I am doing)

I think this is clear.

Many thanks in advance for any light you can shed on all this.

LAFRONDE_GESTIONCopy.fp7.zip

[comment]

There are two relatively simple methods to achieve this:

1. Find the payslips of the relevant year, and summarize them by payee.

2. Define a new relationship between payees (Contacts?) and PaySlips, filtered by year (the year can be selected in a global field). Define a calculation field in the payees table that sums the related slip amounts.

Now I am sure that you alarm bells have already sounded

Correct. There is no reason for this duplication. The changing address can be dealt with by a lookup, and other issues can be handled by field validation and user privileges.

[Søren Dyhr]

but there is a simple reason why I have such a complex copy script

No there isn't at all, keep things indentical field wise in the same table, and tag them with an attribute - to let the database make the distinction between each set.

There is then no need to tie everything together in one spaghetti mess - make a TOG for each layout only consisting of the relevant TO's:

What you have missed in the hurry is that the relations graph isn't a ERD:

http://www.fmp.it/download/files/FM7_key_concepts.pdf

--sd

[chlowden]

Thank you both for your comments. Before I started this db, I watched a Lynda Filemaker tutorial & tried to be a good student. I did a ERD and most of the workflow, worked. But as I developed the db, the Lynda rules seemed to start flying out of the window. I have spent an incalculable amount of hours fiddling around trying to get the TO graph to work & there are still bits that I can't fix & I can't see why. Now I read the pdf and discover that what I had been lead to believe isn't quite that ... but it is (sort of). I must confess that it is a little discouraging, but my work so far has saved my wife hours of excel nonsense, so it must be worth it. I will have to reread the pdf to understand what this all really means to the work so far, but I have a very horrible suspicion that I have to start again. One thing I still don't understand is how to 'block' information. I created an entry form system that creates an entry that I call an archive. The flow of information starts only with the archive & not the entry form. If I understand write, you say that is not right and that there are duplicate fields. No information is held in the entry form apart from calcs. It is the archive that keeps all the info. If I don't have this separation, the information flows freely, making it possible to change info. I see FM like plumbing, the info flowing from one table to another, each table having a consequence on the flow. I was lead to believe that the golden rule was that the flow went only in one direction, from one to many, like roots of a tree. I have often found myself stuck in loops (this being one of them) and it seems that neither my concept of FM nor trees like them. Anyway, I will read and view all that you have supplied and see how I get on.

Many thanks again.