Unable to get GetSummary to work

[Oyseka]

Hi All,

I have two related tables with various subsummary parts and I am trying to get a calculation that combines the summary fields across the two tables. In theLine_Items table I have a Period_Vat summary field which is defined as :

(GetSummary ( VAT_Sum; D_Month )-GetSummary ( Out_VAT_Sum ; D_Month ))-GetSummary ( OrderLines::Period_VAT ; OrderLines::Month)

The period report script performs a find in the Order_Lines table and sorts the records by month and then performs the same find in the Line_Items table and sorts by D_Month but the calculation is not bringing in the summary from the OrderLines table. Any ideas please

[Vaughan]

I doubt that performing maths on summary fields will work. Instead, create a calculation field to do the math, then make a summary field of this calculation.

You haven't yet said what it is you're trying to achieve.

[Oyseka]

I doubt that performing maths on summary fields will work. Instead, create a calculation field to do the math, then make a summary field of this calculation.

You haven't yet said what it is you're trying to achieve.

My apologies Vaughan, the field is a calculation field not a summary field.

[Oyseka]

I doubt that performing maths on summary fields will work. Instead, create a calculation field to do the math, then make a summary field of this calculation.

You haven't yet said what it is you're trying to achieve.

Sorry, I did not address the later part of the question. I have a weird VAT calculation requirement that I posted here, http://fmforums.com/forum/topic/77734-calculation-on-lookup-based-on-stock-type/ I am now trying to combine the results from both tables to give a report of due date VAT.

[comment]

I am not sure what all this means either, but GetSummary() will not work with a breakfield from a related table:

GetSummary ( OrderLines::Period_VAT ; OrderLines::Month)

You need to add a local calculation field = OrderLines::Month and use that as the breakfield instead.

[Oyseka]

I am not sure what all this means either, but GetSummary() will not work with a breakfield from a related table:

You need to add a local calculation field = OrderLines::Month and use that as the breakfield instead.

Hi Comment,

I created a field called Breakfield as a calculation in the Line_Items table with the calculation being =OrderLines::Month and then changed the Period_Vat calculation to (GetSummary ( VAT_Sum; D_Month )-GetSummary ( Out_VAT_Sum ; D_Month ))-GetSummary ( OrderLines::Period_VAT ; Breakfield) but it is still not bringing in the data I have attached screenshots of the summary from the OrderLines table which shows the VAT summary of R9349.56 which is what I am trying to bring into the calculation field Period_VAT_Due

[comment]

Sorry, I cannot follow this.

[Oyseka]

Sorry, I cannot follow this.

Hi Comment,

My post was just confirming that I had followed your suggestion to create a local calculation field as the breakfield.

In the first screenshot which is from the OrderLines table there is a summary field Period_VAT which is in a sub summary part sorted by Month. In the second screenshot from the Line_Items table there is a calculation field, Period_VAT_Due which is also in a sub summary part sorted by month.The calculation in the Period_VAT_Due field is meant to give me the VAT_Sum, which is a summary field, minus the Out_VAT_Sum, which is a summary field, minus the Period_VAT, which is also a summary field, which in this case it should give -R9276.14

[comment]

Perhaps you should post a simple file showing the problem (and not much else).

[Oyseka]

Perhaps you should post a simple file showing the problem (and not much else).

I will be quite happy to, it is 4.3 meg is that OK, (and please don't laugh at it, it is still very much under construction)

[comment]

A 4MB file is probably not "a simple file showing the problem (and not much else)".

[Oyseka]

A 4MB file is probably not "a simple file showing the problem (and not much else)".

I'm sorry but I don't know how to split it, the tables are related to each other and there are both products, clients and suppliers to test the paths. I have deleted all of the products that do not have stock assigned and all but two of the clients which are the ones where an invoice is produced and it is now down to 3.5 meg. Can I delete the layouts that are not involved with the calculation ?

[comment]

[shrug] You can do whatever you think necessary - I'm only trying to tell you that my time and energy are limited, so I will not hunt in your file for the relevant parts.

IMHO, it would be best for you if you tried to work out the problem in a new file, containing only the parts that are necessary to reproduce the problem. Then, if you still cannot solve it, post it here.

[Oyseka]

OK Comment,

Thanks for trying to help anyway.

[Oyseka]

[shrug] You can do whatever you think necessary - I'm only trying to tell you that my time and energy are limited, so I will not hunt in your file for the relevant parts.IMHO, it would be best for you if you tried to work out the problem in a new file, containing only the parts that are necessary to reproduce the problem. Then, if you still cannot solve it, post it here.

Hi Comment,I did as you suggested and started again with just the parts needed to try and get the calc to work and I still can't get it right. In the Line_Items table there is a field called Period_VAT which is a calculation field and it is that that I can't get to work. It is meant to calculate the VAT_Sum, which is a summary field, minus the Output_VAT_Sum, which is a summary field, minus the VAT_Sum from the Order_Items table which is also a summary field. I have done as you suggested and created a local calculation field for the breakfield but it will still not bring in the VAT_Sum from the Order_Items table. If you have time to look at it I would very much appreciate it.

Calc_test.fp7.zip

[comment]

Ahm... (scratching head)... Is -10434.49574999999977335 the correct result for your example?

[Oyseka]

Ahm... (scratching head)... Is -10434.49574999999977335 the correct result for your example?

No, it's -10361.07 (to two places)

[comment]

It is meant to calculate the VAT_Sum, which is a summary field, minus the Output_VAT_Sum, which is a summary field, minus the VAT_Sum from the Order_Items table

OK. You need to explain how you got that result, because going by your description above I get:

VAT_Sum = 489.4925000000000182;

-Output_VAT_Sum = 244.7462500000000091;

-VAT_Sum from the Order_Items = 10679.24199999999978245;

and that sums up to what I said earlier.

[Oyseka]

OK. You need to explain how you got that result, because going by your description above I get:

VAT_Sum = 489.4925000000000182;

-Output_VAT_Sum = 244.7462500000000091;

-VAT_Sum from the Order_Items = 10679.24199999999978245;

and that sums up to what I said earlier.

Hi Comment, if those are the numbers in the layouts then , yes that is correct but on my Line_Items layout the second item is Code P0005 2.4 D AMINE (Ret) 25 LT PLAASKEM and because it is Stock_Type Retail the Output_VAT is R0.00, but yes, your numbers are correct.

[comment]

on my Line_Items layout the second item is Code P0005 2.4 D AMINE (Ret) 25 LT PLAASKEM and because it is Stock_Type Retail the Output_VAT is R0.00

It is the same on my Line_Items layout (I am using your file) - and still the numbers add up to what I said. Could you give a clearer answer? If the method I was going to suggest does not provide the expected result, then obviously it's not a correct one.

[Oyseka]

It is the same on my Line_Items layout (I am using your file) - and still the numbers add up to what I said. Could you give a clearer answer? If the method I was going to suggest does not provide the expected result, then obviously it's not a correct one.

The Line_VAT and VAT_Sum are the same as yours giving a VAT_Sum of 489.49, however my Output_VAT Sum is giving 171.32 only showing Output_VAT on the first Line Item so my fields show

VAT_Sum 489.49

Output_VAT_Sum 171.32

Order_Items::VAT_Sum 10679.24

Which is why I said -10361.07

[Oyseka]

It is the same on my Line_Items layout (I am using your file) - and still the numbers add up to what I said. Could you give a clearer answer? If the method I was going to suggest does not provide the expected result, then obviously it's not a correct one.

Sorry Comment, I know what has happened, your numbers are correct. After I had posted the file I realised my Output_VAT calc was working on the retail price not the cost price and I changed it. Very sorry.

[comment]

I think the basic issue here is relationships, not calculations or summaries. You cannot get data from a found set of another table. Try the attached:

Calc_test 2.zip

[Oyseka]

I think the basic issue here is relationships, not calculations or summaries. You cannot get data from a found set of another table. Try the attached:

Yeeeeeeha, well that definitely works. I cannot thank you enough for your perseverance. So basically the GetSummary function is only for calculations, perhaps on different layouts, but within the same Table. I can find no documentation on that anywhere and I have looked in the FilemakerPro Bible and The Missing Manual. Your help is appreciated.