using date ranges with summery fileds

[bucky k]

I am new to fm9 and databases in general. can do anything in excel. not so here. could someone post the syntax for using a date range in a form to have a fileds total between the range?

i want to show sales within a date range and then total for the month.

thanks bucky k

[LaRetta]

Welcome to FM Forums, Bucky!!!

i want to show sales within a date range and then total for the month.

The most important question here, I think, would be how you need the totals displayed or used. Do you need the totals to display on a form or a list or can it be a report (in preview mode?). Do you need to be able to allow a User to change data in some of the fields on the 'form' while the total is being displayed? Or do the totals need to be exported?

Once we know that, we can give you methods of achieving it. The standard answer would be to use a columnar report with grouped data and sub-summaries. Find records for the month, switch to report, enter preview mode, done ...

LaRetta :wink2:

[bucky k]

i just need to make a preview report that i can then save as a pdf and e-mail out. Noone else is using this data.

bk

[LaRetta]

Create a new layout. Select columnar report. Select Grouped data and sub-summary. Select the fields to put on the report (body). Select the fields to categorize by (in your case it would be month/year). If you don't have a field, I would suggest you create a calculation (result is date) of:

YourDate - Day ( YourDate ) + 1

... where YourDate is the Sale Date or whatever date you want to categorize by month. This will create a date where every date is the first of its month & year. This calculation date can then be used to Group your sales.

So when report asks you what field to use to group (categorize), select this new calculation date. Then you will be asked what to summarize. You will need to open Field Definitions and create a field called s_sale_amt (or whatever you wish to summarize). Type will be SUMMARY and 'total of sale_amt. Back to report specification, select this new summary field and specify ADD to insert it into the box below.

FileMaker will offer to create a script for you. Let it. Because you MUST sort by this new calculation date or it won't work. So let FM create the script which will:

Go To Layout [ this new report you created ]

Sort [ by calculation date ]

Enter Preview Mode

Enter Browse Mode

Go To Layout [ Original Layout ]

... that's all pseudo-script but, as you create the layout, watch the lower right and it will show you exactly what your report will look like. Good luck to you and again, welcome! If you have further questions, let us know.

LaRetta :wink2:

[bucky k]

Thank you so much for your help. I was able to produce a report but... i really would like to have a field area on the report to change the date range. not just have a fixed date. In other words, "show the sales made by region between 4-1-08 and 4-15-08" etc.. I just want the total of the sales not all the individual ones. the total should change as i change the date range.

[Søren Dyhr]

When in preview mode isn't there much more you can do, this is why LaRetta asked about if you needed things to adjust - which only could happen in browsemode.

This will require either a realtional approach or hefty use of custom functions, there is an excellent template here to study:

http://www.nightwing.com.au/FileMaker/demos8/demo803.html

...another good one to get wiser by is this:

http://www.kevinfrank.com/download/kf-fast-summary.zip

But the later doesn't behave inside the spreadsheet metaphor!

--sd

[bucky k]

ah! Please forgive my nubieness( is that a word), Thanks for the links. i need to get an education and I appreciate you taking time to help. There is one other issue that filemaker techs took hours to "fix" I sometimes get ID numbers that appear in a text field from a pop up instead of the names that are supposed to. not all the time. The preview layout we just did had 12 names 6 of which appeared as ID numbers?

[comment]

And how about something simple?

Simple.fp7.zip

[Søren Dyhr]

Yes, hence my putting it this way:

This will require either a realtional approach

Including the typo as well!

--sd

[comment]

: I am not using a relational approach (typo or not). I am not using Preview mode. I am not using a custom function (hefty or not), or a fast summary. I am not... well, you get the point. Or do you? It seems that whenever someone asks how to crack a nut, the first thing you suggest is a steamroller?

[LaRetta]

:jester: ... and he's not lazy enough!

[Søren Dyhr]

:idot: Oh yes this one was scripted - Steamroller indeed, my head is biased towards getting the scripting down to a minimum - unfortunately even when it doesn't make sense any more.

Just remember what OOD was invented for, to prevent spaghetticode .

Next objection must be, if we with matters dealing with our society here as well take a lazy attitude, and cut corners as soon as possible, are we kind of endorsing corruption and the will of the most violent or the one who can pull on the most substantial legacy.

--sd

[comment]

FYI, lying and stealing takes a lot of energy - lazy people are honest.

[Søren Dyhr]

Ok here comes in a prussian angle to the matters, climate does in some locations dictate when to harvest, probably the single most important condition for the bare existence of work ethics at all.

Unwillingness or laziness if it spreads too wide ...to participate in the common project of getting the harvest safe to barns, is preventing bread on the tables during winter - at least this the origin for Max Webers ironcage historically.

All moral is when it comes to it, guidelines for redistribution of resources, it just finds different shapes and forms pretty much according to what the climate it's supposed to work in.

Perspective and planning ahead this distribution will demand sacrifices of the individual - and more switching the conditions are the more are individual forced to recognize the wealth of the entire community, trading in his/her selfishness.

The danger however is that Kafkan mazes evolve as tumors when people gets too eager to sacrifice themselves for the community - checks and balances are required here, the lazy couldn't care less!!!

--sd

[comment]

You are merely recycling the fable of the ant and the grasshopper:

http://en.wikipedia.org/wiki/The_Ant_and_the_Grasshopper

Alas, you and Aesop couldn't be more wrong. Ask yourself this: at the end, who worked harder? Was it the lazy ant, that was able to sing away the winter, or was it the diligent grasshopper, toiling to scrape leftovers from under the snow throughout the coldest season of the year?

[bucky k]

Ok guys , I think you misunderstood my reply before, I DIDN'T MEAN TO SAY I NEEDED TO GET AN EDUCATION FROM YOU!!!!!!!1 I JUST MEANT I HAVE A LOT OF WORK TO DO and a lot to learn. AND YES IT WAS A TYPO!!!

Got the point however, not lazy just stressed. I truly appreciate what you have given me and the "simple file" helped me to understand more about structure and syntax. thanks all for your help.

[mr_vodka]

Hi Bucky.

The subsequent posts were not directed at you. It started from this thread.

http://fmforums.com/forum/showtopic.php?tid/194792

It was not a critique that you were lazy. : It was Michael stating that sometimes people overthink things when the easy answer in right in front of them...