Monthly Totals without using Preview Mode

[THJ]

Hi,

I have FMP 7.0 on XP and am developing a magazine ad sales tracking system to tally monthly sales actuals vs budget as well as variance and variance%. I am looking for a way to do this WITHOUT using a report in preview mode/sub-summaries, I'd want to display it in browse mode WITHOUT the underlying detail.

The layout will look like a spreadsheet and have 4 columns C1:BUDGET, C2:ACTUAL, C3:VARIANCE, C4:VARIANCE%. Each column will have 12 rows, 1 for each month R1:Jan-R12:Dec.

I need the value for each column/month budget stored in a different field on the layout. The easy one was BUDGET which I have stored in 12 fields as a global value. The ACTUAL will be the actual results for a given month. The VARIANCE is a calculation field of ACTUAL-BUDGET and VARIANCE% is a calculation VARIANCE/BUDGET.

I have a simple 1 table file with 3 layouts BUDGET, ACTUAL vs BUDGET, SALES DETAIL. I have upload the file if it helps to look at.

Is there any way to do this with a single table via a calculation or custom function WITHOUT using a report in Preview mode?

Thanks,

Tim

SalesGoals.zip

[Pim Jonkman]

dear thj,

Please let me know if you get an answer! I have a similar question. I'mm working on FM 6 but who knows, it might help.

Best regards Pim Jonkman (Sope Design Strategy)

[BobWeaver]

You could create a separate table for summary information, and use aggregate functions like sum() count() etc. to summarize and display the data from the main table

[Ugo DI LUCA]

Hi,

The file attached does it with only calculations and repeating fields. It was only destinated as an experiment that repeats may work with relationships too, with only calcs. So it is slow.

But you can get the principals and adapt it to get your results into each cell of a repeating field through a looping script instead.

HTH.

RelInRepsCalc.zip

[Pim Jonkman]

Stupid maybee, but i don't understand the extra table. Could you explain further??

[Pim Jonkman]

Hi UGO,

Do have such a file also for FM6?? It might help me.

Thanks in advance.

PJ.

[Himitsu]

just make a relationship to itself then put a field with summary of which field you want to show. The portal will look to itself and show the results as you want. I use this a lot to show only the data that has similar components or parts but lay in the same table. Just set what equals what so you can pull the info from that relationship. Think open with it. Such as dates, those being similar can pull info for all records with those dates. if you match payment types, you can view only what you want that is consistant with the record you are looking at. Play with it. I use them to even filter popup lists to display only the info that catagories have.