Issue with Sub-Summary

[amidyne]

I'm having a slight problem with the sub-summary display. Currently I am trying to display information from several product series, and as far as I can tell, I have setup the subsummary correctly. However all the information that gets displayed is the first record from each series.

I.E. I have 50 different records associated with product series A, only record 1 is present.

I should add that currently product series is the only field in the sub-summary part and the rest of my fields are in the body

is there some setup issues that I have overlooked? Or is it completely different.

Any help would be greatly appreciated

Edited August 11, 2008 by Guest

[amidyne]

I discovered the problem WooHoo! I was trying to display the summary in the parent table. I should have been displaying from the child summary. However now I'm not displaying the sub-summary field, but I think I'm getting there.

Any suggestions?

Edited August 11, 2008 by Guest

[Søren Dyhr]

Genuine summary fields are a tad too reluctant to refresh properly when seen from the parent table, instead use the aggregate-function pendant to it ...then approaching the found set over the relation from parentside.

To get a balanced view of when to use which of them is this template pretty good IMHO:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

Sub-summaries require an entire different strategy, which I with the indicated level of filemaker skills shown in this post - would be something you won't get the gist of immediately. Instead would I urge you to investigate the template and see the point in making the sub-summaries in the most atomic part of the solution.

--sd

[amidyne]

Thanks for the tip on the sub-summaries. I actually discovered that I had neglected to tell filemaker to sort the records. Which was why the records were showing up like a list instead of being divided by the product series.

[amidyne]

Ok not to open closed wounds but my sub-summary has created a new issue. Everything is working adequately. The records are being sorted correctly and all data is present. However, my database has created some orphan records, they don't sort correctly even though I have indicated the correct product series for each record.

(I now have a category that shows products from product group A, B, C, etc.. even though they I have indicated where they should go)

I've tried deleting the individual records and re-entering them, thinking that I may have made the mistake on the outset however the record just reappears at the bottom of the un-desired list)

I hope I have described the problem thoroughly, if not I can post a screen cap to show what I'm dealing with.

Any help would be greatly appreciated

[comment]

Check your data: most likely, those records do not match the exact category name (leading or trailing spaces/returns, etc.).

If your category field is formatted using a value list, place another instance of the field on the layout, format it as edit box, and see if there are additional values in the field.

BTW, this is a good illustration of why ID's work better for relationships than names.

[amidyne]

Thank you comment. You were right on the money with the value lists. As it turned out, my database was being sorted by the wrong one (two value lists, similar values) Everything is working as it should for now

[amidyne]

So everything is working, However my boss was hoping to have a little more. Currently if there are no records displayed for a product series, the sub-summary part does not display. What he wants is for the part to display all the time,

(i.e. If there are no visible sales orders for product series A then product series A does not display)

Is there a way to get my desired outcome without having to create blank records associated with the product series?

[Vaughan]

"Is there a way to get my desired outcome without having to create blank records associated with the product series?"

No, you'll have to create records that contain zero amounts.

Or talk your boss out of the idea. Explain how bad it will get when there are thousands of products and only hundreds have non-zero amounts.

[comment]

Of course there's a way. It's just not as simple and elegant as reporting from the child table. But you can report from the parent table, with children shown in a sliding portal. Or you could compile your report by script into a global text field.

[amidyne]

I've gone the sliding portals route and that was generally panned because of how it looked. I'm assuming the global text field will look generally similar. I'm thinking of trying to get Filemaker to create a new blank record for the product series if there are no other records. And then delete that blank record once a new record is created. But I'll switch over to the scripting forums to discuss that option

[Søren Dyhr]

There is another way if the sliding is required:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

But genuine sumary reports are still the way to go, since the just mentioned approach really hogs resources when you try to render such a layout. Similar is Ugo's method charmingly clever, but there is still a rendering price to pay.

--sd

[comment]

I've gone the sliding portals route and that was generally panned because of how it looked.

That's not very clear. Sliding portals can look resonably good - provided you invest the required effort.

I'm assuming the global text field will look generally similar.

Ditto.

I'm thinking of trying to get Filemaker to create a new blank record for the product series if there are no other records.

I would never create dummy records in my REAL table. If none of the other options are acceptable, consider importing the existing records into a temp table, and adding the dummy records there.

I'll switch over to the scripting forums to discuss that option

It's best to keep a topic to a single thread - the moderators will move it to a more appropriate section, if they think it's necessary.

[amidyne]

That's not very clear. Sliding portals can look resonably good - provided you invest the required effort.

This dislike for the sliding portals method came about when I was away for a week from work, needless to say when I got back I was told to change the layout...

But I digress, I'm still trying to decide which route to go with getting all product series to display, but now another issue has cropped up!

Right now all products get sorted into the correct product series as it should, but within each product series lies the problem. Generally what happens is that after getting sorted into the groups, the products are further sorted by sales order.

[All products associated with sales order # 32123 comes before all products associated with sales order # 33212]

But on occasion this will not happen and the sales orders will appear out of order and not grouped correctly.

I've tried changing the sort order to include different sorting criteria (sales order and order date) but to no avail.

So initially I thought that I would just try and re-enter the record and hope for the best, but in fact this pushed the data further out of order.

I'm going to try and describe the way the layout is setup and my associated script to get my order. If this you want a clearer picture I will send a screen cap of what's going on to you directly.

Layout Order:

Sub-Summary When Sorted by Product Series (Only data here is the product series #)

Body (All related data for the sales order)

Sub-Summary When Sorted by Sales Order # (Only data here is a SubTotal to get the value of the Sales Order)

Sub-Summary when sorted by Product Series (Only data here is a grand total for the the Product Series)

That's it for the layout my script follows // will indicate what each line does

Go to Related Record [From table: "Status Log";Using layout: "List View - Status Log" (Status Log)

Perform Find [Restore] //I'm filtering out all shipped product from being displayed

Sort Records [Restore: No Dialog] //Sorting with a custom Sort Order for Product Series followed by a sort by Sales Order

Enter Preview Mode [Pause]

Enter Browse Mode []

I hope that everything has been explained correctly and succinctly

Edited August 13, 2008 by Guest

[comment]

I don't understand this part:

[All products associated with sales order # 32123 comes before all products associated with sales order # 33212]

Perhaps you should attach a simplified file showing the problem (a single table with some sample data should be enough).

[amidyne]

I'll continue to work on the sample file but for the time being here's a capture of what I was trying to describe. As you can see under 9284PA sales order 32243 comes before sales order 31440

Also shown is the product series 9283, which is the way I would like the sales orders to sort

[amidyne]

Ok I Fixed the sort Issue!!!

It was pretty much identical to my value list sort issue. Two fields named Sales Order# on different tables. I had decided to sort by the wrong one...

..Still working on my blank product series though, but this was a BIG step forward