Sub-summary fields not calculating correctly

[scarlet23]

I've made a 4-file relational database as part of my IT course. The related files are called 'clients', 'sales', 'products' and 'orders', with the orders file as the main file. Everything is working as it should be, with the exception of sub-summary fields in an automatically generated report. The report is supposed to summarise all products sold within a chosen month. The month and year is selected via a drop-down menu on a separate layout, and the script that generates the report uses this information to search for relevant products/records. The invoice/order layout features a portal where products can be added to a single form and printed with the client's name.

I have used sub-summary fields to summarise how many of each product has been sold in the specified month. The problem is, the sub-summary fields don't add all of the products into the summary (see number 1 in diagram). I think this might have something to do with the portal and have noticed that the sub-summary fields also seem to disregard the quantity of a product if more than 1 was purchased at a time (see number 2 in diagram). Also, the same product may appear more than once in the report if it had been ordered more than once in the specified month (see number 3 in the diagram). I would prefer each product to appear only once, but this is not vital.

If this sounds confusing, please don't hesitate to ask for clarification and thanks for your assistance.

[Vaughan]

It would helpful if you posted a pic of the report in Layout mode, so we could see the sub-summary parts.

[Søren Dyhr]

Try to compare my attachment below with your version of the matter. Pay attention to sort-fields (in plural) and the two summary fields and which of the tables I perform the summary report. I have gotten rid of the body part! Now the Itemprice used in the sales table ought actually be a lookup, in case you are using it for invoicing, since it then becomes a historic document, where changes in prices not should tamper with old invoice sums.

--sd

Glam.zip

Edited July 3, 2007 by Guest
Was talking in riddles!

[scarlet23]

Thank you so much! This problem has been bugging me for ages. Ah, such relief!

[scarlet23]

Argh. I've just noticed another problem. I worked on it for ages, but can't figure out how to fix it. The report isn't showing all products sold in the specified month, only the first product to appear in the portal in the invoice layout. The portal shows records from the sales file, shown in the attached diagram. I produced the report for June 2007, also shown in the diagram, and I know there are much more than 3 products sold in that month. Help?

[Søren Dyhr]

What kind of mechanism do you use to ensure the field you search upon holds values? In my template was a autoenter utilized! Another question is Why is the foreignkey a dropdown menu??

Some sort of non empty validation must take place, to prevent report orphans ...which doesn't show up in report, even though it arrives safely in the portal.

Then might there be something in the field types utilized? Couldn't you either off forum send me a clone or upload it here??

--sd

Edited July 4, 2007 by Guest
more thoughts

[scarlet23]

I'm sorry, I don't understand Could you please clarify exactly which part of the database you are referring to? Also, when performing a search for products sold in a specified month, the user is directed to a layout which has a drop-down menu for both a 'month' and 'year' field using value lists created from the 'invoice date' field.

By foreignkey, do you mean the 'client ID' field? It is a drop-down menu as a form of validation; to ensure the user of the database does not enter a non-existant Client ID.

[Søren Dyhr]

OK that makes sense, since you think that the "Allow creation of related record" looks ugly with the dongle or phantom-line??

Could you please clarify exactly which part of the database you are referring to?

I've drawn some boxes around where I would be suspicious, take a look at the above image!

--sd

[scarlet23]

OK that makes sense, since you think that the "Allow creation of related record" looks ugly with the dongle or phantom-line??

I'm even more confused after reading this :S Are you referring to the lines in the portal in the invoice layout? I don't know what they are from I allowed the creation of related records when I made the portal, too. The records are all there as they should be, in the sales file (the file that is linked to the portal). They just don't appear when I perform a search, which I think is because the records I am searching for are in the sales file, not the main file. I don't know how to make them show up in the report.

Edited July 4, 2007 by Guest

[comment]

Your reports should be based on the sales table (commonly known as "line items"). To print an invoice (or multiple invoices), sub-summarize by OrderID. To print a report of sales by product, sub-summarize by ProductID.

[scarlet23]

My report is based on the sales file, it's just not located in the sales file. From what I can see, making a new report layout in the sales file is the only way the report could work, but I think I might lose marks for that because it's not very user friendly... I have sub-summarised by product ID and client ID, as in the files posted by Søren Dyhr. I can send the entire database to you guys to look at, if that's easier, as I'm a bit confused right now :S

Edited July 4, 2007 by Guest

[Søren Dyhr]

I would like a look!

From what I can see, making a new report layout in the sales file is the only way the report could work, but I think I might lose marks for that because it's not very user friendly

It's common sense to make the reporting in the table with the "many" aspect of the one to many relationship. Which a many to many technically is a pair of!

Then is there your talk about files vs. tables. This is post FM6 but it's apparently a migrated solution - so it's much more convenient to have a file where all layouts reside, eventhough it questionable if the separation model really should be engaged ...is it here perhaps too far fetched.

But it still pay off to keep all involved tables in the same file. Reportings are bound to lead the user to a specialized layout anyway ...so you're better off keeping it in the same file, to prevent users get confused by several open files, some minimized and some not.

--sd

[scarlet23]

Here's my database. Hopefully you can pinpoint the problem from there...

Project.zip

[Søren Dyhr]

The database must have at least three linked files

This could be two datafiles, and one interface file. Where one of the datafiles stores your images in containerfields. Take a look at this template:

http://www.newcenturydata.com/downloads/separation_demo.zip

It is indeed counter intuitive to the user to deal with more than one file! My take on how this have found it's way into you assignment is that someone (Your tutor?) not quite are aware of the newer features filemaker such as having multiple tables in one file - or just being unhappy to change the assignment!

You have shout up an tell them that the requirement is an impractical bygone, we all still are celebrating the departure of ...it's unfair if you recieve lower grades if you make this reservation. What you have to display is knowledge to relational structures and in particular many 2 many.

What you have to do is to merge all tables into one data file, put all your layouts in another and finally put the image/artwork in yet a separate file!

All in all is the problem that you report from the wrong table, like both Comment and I have pointed out!

---------------------------------------------

A saved by the bell solution to your problem is to usher the sales table into the Order file, and let the remaining files be as they were before this quick fix, since most of the business goes on in the order table.

Everybody is pleased, 3 files and a demonstration of both options. The time it takes to make it depends a little weather you have fm8.5adv to you disposal, or need to do the donkeywork of typing in each of the field in the newly created table.

--sd

Edited July 4, 2007 by Guest

[scarlet23]

Part of my assignment is that I have to have 4 related files. I think it might bee too confusing for both me and my teacher if I change it now. Just to clarify, you are saying that for the report to work correctly, it must be in the sale file? Are you sure there is no other way?

[Søren Dyhr]

The demand of making it in the correct table, is pretty rigid but there are this:

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

in order to send it to the desired tables interface ...or you simply provide you userinterface dealings with these:

http://www.filemaker.com/help/ScriptsRef-24.html

http://www.filemaker.com/help/ScriptsRef-23.html

...but they're not striking me as elegant, since you have a small brickwall in the lower part of the app's window, when the user expirience is the non mac os x'ish.

--sd

[Søren Dyhr]

Part of my assignment is that I have to have 4 related files

Which in my humble opinion is pretty daft, at least is it ignorant!

--sd

[Fenton]

Reports are almost always in the table that the granular data exists. Also, there is no reason why the Sales table should not be in the Orders file. I have to say that if your teacher is not yet aware of and teaching the modern multiple table capability of FileMaker (7 on), they do not seem competent to teach FileMaker.

I created one version where the Sales table was inside the Orders, no Sales file. That took about 15 min. Then I relented and restored it as a separate file. That took about 5 minutes. That is how easy it is. It's just a File Reference and a table, with its table occurrence on the graph.

One thing. There should be no Product ID in Orders. The connection to products is always thru Sales. How can an Order target a specific product?

To produce the report, it's more convenient to have the Client ID and the Date Order in the Sales file, for local sorting for the Subsummary parts. They can be looked up, as they are constant for any given Order, established before Sales items are added (or should be).

I just switched your report layout to be a Sales layout. Many of the fields realligned themselves automatically (Sales), but a few needed to be changed.

Another thing. You don't really need to go through conniptions to produce a month name for a date. Just use the Format Date options. You can specify a date to appear as only month name and year (one assumes that the 1st record's date is in the month and year, if you're doing a "monthly" report).

I changed your "choice" month and year fields to be globals, so they can be passed to a Find in Sales.

As you can see, using another file/table does not necessarily change the user interface at all. It's all scripted. They cannot really tell the difference between a Sales based layout and an Order based layout (apparently neither can your teacher -).

Sorry, but IT people who pretend to know (and often criticize) FileMaker, but are years behind in their knowledge, bug me.

Project_fej.zip

[Søren Dyhr]

That took about 15 min. Then I relented and restored it as a separate file. That took about 5 minutes

Indeed, a bit of both the separation model as well as multitable in a single file - it just came to me as well!

--sd

[scarlet23]

Sometimes, in school, they get us to do things the long way first, then show us the shortcuts, so we understand how everything works. Maybe that's why my teacher did things this way?

Anyway, the changes that Fenton made seem to work, it's just that I don't understand how you places the sales table in the orders file. Sorry, but I have a lot of documentation ahead of me (you might have seen it in the folder I sent you), and I have to be able to explain how everything was done.

What is the 'Preview print many letter' script used for, and are the error checking and other script steps in that script necessary? I haven't been taught how to use some of those, so it might seem suspicious.

Edited July 5, 2007 by Guest

[Søren Dyhr]

just that I don't understand how you places the sales table in the orders file

As soon as a file reference is established, say by defining a relationship, can you when defining a new layout use this file-reference in the file you're working with. All tables shown in the RG can be used to the creation of a new layout, in the present file.

What is the 'Preview print many letter' script used for

If your report have many pages will there be no measure of how many pages you're likley to print out.

By going initially to the last, will the report generator give you both values an not just the first and a ? where Total was supposed to hold a value. It's just a measure to make sure that the user really wish to occupy the printer with say 1300 pages, preventing others in the office to anything with the printer for say an hour or such.

--sd

[scarlet23]

My database is completed now, I ended up scrapping the monthly product report and doing something slightly different and less complex. Thanks for all your help!