Adding field from multiple records within a date range

[sicSRT8]

How would you sum a field from multiple records during a specific date range. Would this be in a calc or does it need to be a script?

Can you provide an example please?

attempting to create a report dashboard but not sure exactly how to sum fields from multiple records within a date range.

[LaRetta]

"how to sum fields from multiple records within a date range. "

It sounds like you want to perform a find for the date range then generate a grouped report with sub-summary. Can you provide specific example of what you want? :smile2:

[sicSRT8]

Sure.

There is a field (total items) on each record. Each record is also marked with a date (date)I am trying to add the (Total Items) from each record from all records with the (date) of 01/xx/2009.

So in one field it will have a total of items from all records with the date of 01/xx/2009. In otherwords all records during the month of Jan of 2009.

[LaRetta]

To group and summarize by month/year, the easiest (and most versitile) is to create a calculation (result is date called cMonthDate) with: DateField - Day ( DateField ) + 1. This will turn all of your dates into a generic 'first day of month.'

You then create a grouped columnar report (let the layout Report Assistant create it for you) with leading part based upon cMonthDate. Place this field in your leading part and, from layout level, format the date to only display month/year as January, 2010.

Create a summary field (called s_TotalItems) which is 'total of' and your TotalItems field. Place the summary field in leading part. Sort the report by cMonthYear (which is called your break field). You can delete the body of the report if you wish so, if you find a year's worth of records, sort them by cMonthYear then go to Preview Mode, it will display the totals for each of those groups as:

November, 2009 - 327

December, 2009 - 424

January, 2010 - 32

If you need something different, we can help you through it. :wink2:

Edited January 22, 2010 by Guest

[sicSRT8]

yeah I just want to select the month and have totals of fields show up on a seperate table.

I have the fields working where i can see the first day of each date on each record and the total items.

Edited January 27, 2010 by Guest

[LaRetta]

You sure are thin on the information. Well, here's two approaches (both displayed on same MainTable layout). The first shows every record with the date and also shows what the group total is for that same month/year (s_total_items). It uses self join called MainTable_selfjoin.

The second method allows global for cMonthDate selection. And then it displays (and totals) in a self-join portal called MainTablePortal. Note the yellow field below the portal is the same field within the portal called s_total_items. You don't need to display the detail in the portal at all but simply place this field on your layout and select from the pop-up.

UPDATE: Note that the first method uses the related version of the summary field because you want the totals based upon monthdate and NOT the totals only based upon your current found set (if I understand correctly).

TotalMonths.zip

Edited January 28, 2010 by Guest
Added update

[sicSRT8]

I made a generic layout of what may help me better understand and explain what it is im trying to do.

In the reports layout select month and totals for that month show up. Sorry if I seem thin on info and hoping this helps.

example.zip

[LaRetta]

I've been considering your file. I do not believe you should have Apples, Oranges and Pears as FIELDS. What happens when you add peaches? Have you reviewed my file? If the fruits were records, a grouped columnar layout with sub-summary would be perfect and you won't be writing static data to fields.

Is this the true solution you are working on? If you want it exactly as you have it and fruits are fields, you will need a restricted relationship for every type of fruit ... or you can search, and loop/count/set each fruit field.

It all is overly complicated which wouldn't be necessary if fruits were records as:

Record #1

Category: Fruit

Type: Apple

Count: 3

Record #2

Category: Fruit

Type: Orange

Count: 1

Record #3

Category: Fruit

Type: Apple

Count: 2

Then summary report would automatically show:

Leading part: Type

Apple

- Record #1 count 3

- Record #3 count 1

total Apples 4

Orange

- Record #2 count 1

total Orange 1

... etc. Reports RARELY need to be another table with hard-coded data. Unless there is a lot more to your solution than you are explaining, I doubt a report table is needed here (but I could be wrong). Is this helping or making sense?

UPDATE: If you need it more like my second example in that file (far right), then you need a relationship joining apple to apple and another relationship joining orange to orange etc... not a good way to go ...

Edited January 28, 2010 by Guest
Added update

[sicSRT8]

Well there is alot more info but trying to do this in sections so I can actually learn FM by learning what I need.

I am trying to create a report dashboard which will be using graphs but I need to learn how to compile the specific data I want for me to do that.

I want to use selection drop downs to specify what information I want see in the graph. I should be able to figure out how to do that once I figure out how to compile the data based on specific terms. My example of the date compile will show me how to compile based on ranges is what I am ultimately looking for. Does that make sense? Thank you for you help.

[LaRetta]

My example of the date compile will show me how to compile based on ranges is what I am ultimately looking for.

I still don't have enough information to suggest whether reporting, relationships or loop/setting fields is the way to go for you. It sounds like repeating fields with the month/year as extended labels and ...

ah, yes, check out this file SumByCategory by Comment.

[sicSRT8]

I am not sure what info you need specificlly. I assume that using multiple drop downs to get to a specific item would mean I would already have to have the item set in a field right?

So I would relate the data in a way to get those specific fields to show up after the drop downs are selected.

Unless I am using the wrong method to archive this?! I am playing with fusion charts and understand it enough to know how they work but I am trying to get the data to a specific point to use inside a dash board to make it more aesthetically pleasing for my reporting as opposed to using graphs in excel from exported data.

You were talking about what happens when I add peaches and the results I am pulling would never involve peaches. Using the fruit was another way of saying shrink, remake, waste, profit etc...

[LaRetta]

So I would relate the data in a way to get those specific fields to show up after the drop downs are selected.

That is why I explained (and provided a demo) using a global and the relationship to total the group (month/year) records.

I am not sure what info you need specificlly.

... nor I you. I explained summary reports, suggested possible looping scripts to set the data in your reports, presented using relationships to group and summarize data, and provided a link of summarizing based upon repeating fields. I have no idea what your data really looks like (which terribly limits my possible suggestions). And I have no idea what you are currently absorbing and understanding from what I have presented (or from what you hopefully have been finding here on forums).

Maybe others can step in and assist. Because, although I truly want to help further, I simply don't know what else I can provide you. Maybe others can nudge you forward or make suggestions.

Edited January 28, 2010 by Guest
Added quote

[sicSRT8]

K went back thru your demo over the weekend and it was there I was looking at it completely different. The portal is nice but not what I needed and was thinking that the only way I could get the total was from the portal and its not. Needless to say problem solved. Thank you!

[Salesman0Gil]

OK... Here is how I would do it. It probably not as pretty as most, but this was fun to think about.

First make two fields that are date fields, and are globals. One is the start of your range "Start_Date", the other is the end of your range "End_Date".

Make a secondary "Total Items" field.

I am assuming you already have a date in each record. I will reference this as "date" in the calc.

So here is your new total items field.

If ( (Date ≥ Start_Date) and (Date ≤ End_Date ; Total_Items; 0)

Now make a Summary field (Total_Items_Sum) have it equal to total of the new Total_Items

One last filed would be to grab the summary, and this would display your results.

GetSummary (Total_Items_Sum,"whatever you want")

The only problem with this solution is the sort..... for the summary.

There has to be a way to use a straight calc, and not worry about the sort...

Well, anyway that would work