Duplicate an Excel s/sheet using FM 4.0

[TONYP]

Hi. I am a 'very new' user of this forum (as from 31-MAR-203) as well as being in a 'learning curve' with Filemaker and am hopeful that someone

can help me with the following problem. I apologise in advance if this Posted question goes on a bit! Also I wasn't sure if it should go in DEFINE FIELDS

or VALUE LISTS forum - but I'm sure someone will 'put me straight later'.

I am transferring staff records from several excel workbooks into a Filemaker 4.0 database so more powerful searches and reports can be done. I have got the main layouts looking OK and have imported some data for test purposes but I have run into trouble with trying to duplicate a 'Calculation Table' that currently exists as a separate worksheet in each of the workbooks. CAN SOMEONE PLEASE PUT ME OUT OF MY MISERY.

The three fields and their types that are used to create this table are:

POST GRADE = TEXT (indexed by a value list) [e.g. Lecturer, Administrator, Secretary etc)

POST FUNDING = TEXT (indexed by a value list) [e.g. HEFCE, National Health, Special Projects etc]

PERSON FRACTION (FTE) = NUMBER [free entry from a maximum of 1.0 downwards]

The POST GRADE field and the POST FUNDING field both have large value lists and the PERSON FRACTION (FTE) field is free entry.

I have used a Sub-Summary to get the POST GRADE's for any department displayed OK but cannot seem to get my head around the correct calculation to extract the PERSON FRACTION's. My big problem is extracting and totalling the PERSON FRACTION (e.g. 1.0+0.5+0.8=2.3) against any POST GRADE (e.g. Lecturer, Administrator, Secretary) and then against any POST FUNDING area (e.g. HEFCE, Special Projects etc) that may exist in a particular department.

I am looking for something that will give me the Total of PERSON FRACTION when POST FUNDING = Special Projects (or any of the other funding names in the value list)

I know I would probably have to have as many calculations as I have POST FUNDING categories in my value list but that's OK as I can set up layouts for each department with their own POST FUNDING groups across the top in the Header area, it's just getting the first calculation defined that I can then make changes for any POST FUNDING category that is tripping me up. So far anything I have tried to set up to calculate these individual POST FUNDING figures does not work when I place the field on my sub-summary layout part.

I have attached a small Excel file as an example of these 'tables' I am trying to mirror from the database.

I am assuming that my last column, Totals of ALL PERSON FRACTION (FTE), could be achieved by adding up the results of all the individual calculation fields in the sub-summary part using a separate field to do this. Would it be something like SUM(calc field 1, calc field 2, calc field 3 etc) and include ALL the calc. Fields that I create just in case someone is added to a department in any of the Funding Groups?

Any help from anyone in the forum would be appreciated to put me OUT OF MY MISERY!

Tony.

TABLE EXAMPLE RE FORUM.ZIP

[Pupiweb]

It seems to me you need a subsummary report broken by Post Grade first and then by Post Funding

This can be done quite easily using a subsummary layout under the layout wizard guidance

I'm enclosing an example file

I hope I understood properly

New.fp5.zip

[Lee Smith]

Be sure and view the Report layout in "Preview Mode"

Lee

[TONYP]

Lee,

Thanks.

Yes I'm aware that 'Preview Mode' needs to be used to display the finished summarized report.

Pupiweb,

Thanks for your reply and attachment although as it is in FM5 and I'm using FM4 so I can't open it.

Have you FM4 that you could send it to me in?

(A friend using FM5 has opened and saved in csv format for me with a list of field names/types etc. and

I have imported to FM4 and created the fields - hope nothing has been lost though).

Based on my import from my friends csv file your suggestion still leaves unsolved how I can count/total the

FRACTION's for any specific POSTFUNDING in the value list so that I can display it against any of

the POSTGRADE names.

Your 'TOTALFRACTION Summary field = Total of Fraction' only adds up ALL the FRACTIONS for

a specific POSTGRADE but does not split it out into the individual POSTFUNDING headings.

e.g. your file and summary gives;

Administrator = 143 Lecturer = 2 Secretary = 26

whereas what I need laid out across the screen horizontally is;-

POSTGRADE... POSTFUNDING......................TOTAL FRACTION

Administrator:...AGFA = 88....HEFCE = 55.........143

Lecturer:........... AGFA = 1....HEFCE = ..1..............2

Secretary:......... AGFA = 22....HEFCE =...4...........26

Does that make my problem any clearer to you, or anyone else for that matter!.

SITTING HOPING FOR 'A MIRACLE'.

Cheers

Tony [color:"red"] [color:"red"] [color:"blue"] count/total

[Pupiweb]

Sorry, I didn't think about the FM4 issue

I'm attaching a FM4 version of the file: the report is broken by GRADE first and then by FUND

It develops vertically, like

GRADE: ADMINISTRATOR ... total 88

HEFCE ... total 66

AGFA ... total 22

This is the "normal" FileMaker style for reports and FM is pretty good at doing them this way

If instead you want to develop them horizontally you're in for quite a bit of work ... poke around looking for "Gannt charts" and you'll see some examples

SubSumm.fp3.zip