Hi, I am getting an issue regarding Calculating the Total of Each individual Item per month. I have created  2 tables in FileMaker. A Item table which will have ID and Date. Another table LineItems. which will be related to Item with via Items ID as FK in LineItems.

 

http://imgur.com/vdbmhEA

 

I wanted to create a List of items per month based. and there Total QTy per month. Something like that.

 

http://imgur.com/qUWXF8v

 

Of course its showing all the Items sum. I want it to have sum on each individual item i have that month. For Example: Marks are are repeated 2 times in second image. it shouldn't have only it showed once and its total sum.

 

The Files i am attaching has lots of other things. But i have putted all the related layout of the table in sub Folder called Misc

15-Mar-2014.zip

Create a new part , sub-summary  by name.. drag your fields in their and sort by name. and your done

Create a new part , sub-summary  by name.. drag your fields in their and sort by name. and your done

 

Yeah i already did that. It sort out every record by month and add the QTy too. But. If i want the record of current month or last month only! Then how can i filter it?

But. If i want the record of current month or last month only!

 

Assuming that each line item is related to an Invoice (or other transaction) record, which has a date …

 

• either from the Invoices layout, find all Invoices for the desired month, then use the Go to Related Record script step (with the options Related records only…; Match found set) go to all related line items of all invoices in the found set, or

 

• from the LineItems layout, search for the desired month in the related date field from Invoices.

This is what I use

 

If(Year (Extend( ShipDate ))=Year(Get ( CurrentDate ));

Case(Get ( CalculationRepetitionNumber )=Month ( Extend(ShipDate ));

Extend(ItemPrice by ProductGroup2));"")

 

Note the use of EXTEND in a repeating field (12 reps)

 

 

 

This is what I use

 

If(Year (Extend( ShipDate ))=Year(Get ( CurrentDate ));

Case(Get ( CalculationRepetitionNumber )=Month ( Extend(ShipDate ));

Extend(ItemPrice by ProductGroup2));"")

 

Note the use of EXTEND in a repeating field (12 reps)

 

That is nice, can you send me sample file of it? I want to learn how you made it.

I can make you one, give me a day or so  and I will send to you. I obviously cant send you my actual database.

I can make you one, give me a day or so  and I will send to you. I obviously cant send you my actual database.

Ok sure no problem.

Here is your file, let me know what you think

test report.zip

Here is your file, let me know what you think

that was very impressive. I have few questions.

 

1) How will the report work for new year? For Example: If i have product A sold 15 on May 2013 and same product sold 35 on May 2014? will it show 50 in May column?

 

2) If i have 1000 products will it show all of them?

You are correct.  this report is designed for 1 year. on my system I need to go from 2011 to present. so I do 2 reports, I do a yearly one, exactly the same way except in years and not months, the user can toggle between years or months. just create a new sub summary part for years and a new calc and summary field.