Jump to content
Grecomarchetta

Repeating Fields

Recommended Posts

Grecomarchetta

I need to track daily sales over a period of time. Each morning the current amount sold is entered in to a field. These sale items are sold over varying amounts of time - some for 7 days others for 90 days.

 

Is it possible to use repeating fields to track the sales of an item over a period of time?

 

Is it possible to calculate the difference between the new entry and the prior entry - so that the user can see the jump in sales?

 

Is it possible to avg the jump over the prior 5 entries?

 

Is it possible to associate a date with an entry in a repeating field?

 

thanks -

Share this post


Link to post
Share on other sites
AHunter3
I need to track daily sales over a period of time. Each morning the current amount sold is entered in to a field. These sale items are sold over varying amounts of time - some for 7 days others for 90 days.

 

Is it possible to use repeating fields to track the sales of an item over a period of time?

 

 

The only use for repeating fields anywhere, ever, that's not guaranteed to make you bald, give you ulcers, ingrown toenails, cirrhosis of the liver, and recurrent nightmares involving burning children desperately screaming for their mommies amidst twisted girders and flaming wreakage, is for storing global values that you will later reference in calculations for things such as icons and other user-interface deelyboppers. And even then I recommend against them on general principles.

 

Don't put your actual data in repeating fields. I'm not the least bit kidding: you have no idea just how horrible those things are when you want to run reports, do sorting, or just plain old delete some rows of data that you input by mistake.

 

I will come back with some links for you to read on the subject.

 

 

 

The answer to the rest of your questions would be that however possible it might be to coerce a set of repeating fields into behaving in that fashion, it's far far easier to do so with normal data structures. By normal data structures I mean a related table in which each sale (or each figure for aggregate sales per specified time-interval, if that's how you're doing it) is its own separate record.

 

 

EDIT: as promised, links on the subject of repeating fields.

 

http://filemakertoday.com/com/showthread.php?t=14725

 

http://filemakertoday.com/com/showthread.php?t=14420

 

http://filemakertoday.com/com/showthread.php?t=14107

 

http://filemakertoday.com/com/showthread.php?t=13469

 

http://filemakertoday.com/com/showthread.php?t=12914

 

 

...as you will see, it's not just my opinion, even if I'm a bit more emphatic about it than most folks on the board are.

Share this post


Link to post
Share on other sites
Grecomarchetta

I'm already bald but you convinced me with the burning kids nightmare scenario. Point taken and thank you. I look forward to any direction you can point me in.

 

GM

Share this post


Link to post
Share on other sites
AHunter3

The ideal structure would be one in which each individual sale is its own record, but I don't know if you have (or with to ever deal with) info quite that granular. If your figures when they come in are daily figures, each record should be a specific DATE and the other fields would be the quantity sold and the $ brought in by those sales on that date. If your desired report is "per the client' or "per the outlet store" or "per the department" or anything of that sort, and you receive the data broken down in that fashion, you would want each record to also be specific to those elements and a field specifying those values should exist.

 

You should also create these two fields in your table: a numerical calc field defined simply as "1" (without the quotation marks) named "One", and a summary field, let's call it "SumOne", defined as Total of One.

 

You're going to love the reports themselves. You run what is called a subsummary report. Create a new layout of the "list/columnar report" variety and it walks you through the process of generating the report, even creating the script for you. (The items thus created, both the report script and the report layout, often require subsequent editing, but it's of the final polish variety). FileMaker intrinsically does the grunt work for you. When you're walking through the new-layout wizard thingie and it asks if you want grouped data, the answer is "yes" and whenever it asks for a summary field to use for subtotals or grand totals you use SumOne.

 

It's up to you to break down your data how you want it: by date and then within each date by department and then within each department by client, with subtotals per each client and subtotals per each department in toto and subtotals per each date? no problem, effortless. Only need subtotals per each date (the daily sales totals)? easy. Only going to run it for one date at a time anyhow? Just use a grand total. Don't need or don't want to see the individual lines, just need the aggregate subtotals and grand total? Delete the body part from the layout.

Share this post


Link to post
Share on other sites
mfif

That's a great description, AHunter.

 

Wouldn't it do to make the Summary field = Count ( primarykeyfield)? There would be no need for the One field in that case, would there?

Share this post


Link to post
Share on other sites
Grecomarchetta

thx mfif and ahunter3 - this subscription just paid for itself in time saved.

 

Best,

Share this post


Link to post
Share on other sites
AHunter3
Wouldn't it do to make the Summary field = Count ( primarykeyfield)? There would be no need for the One field in that case, would there?

 

Totally true. I am in the habit of doing it with One and SumOne. I have an odd aversion to Count, I prefer Sum(Relationship::One) to Count(Relationship::Serial Number) and I prefer SummaryField=Total of One rather than SummaryField=Count of SerialNumber, but not for any reason I can point to.

Share this post


Link to post
Share on other sites
mfif
this subscription just paid for itself in time saved.

 

Watch out and don't get hooked like I am - all the time I gain eventually gets wasted hanging out here. smiley-wink

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Terms of Use