sorting order disaster

[jst]

Help needed. Sorry if this is a truly newbe problem, but I desparately stuck ...

I defined a db for a shop that offers clothing material to schools, defining, school, term (2003-2004), name and articles needed for the school (shirts, trousers, ...) and these clothes have different sizes (S,M,L, XL, ...). My main fields are article (eg art1) and size (eg size art 1). This is done for 30 articles (... art 30 and size art 30).

When I sort my articles to calculate/summarize totals per article and per size, I don't get my (sub)summary right.

When sorting on art1, size art1, art 2, size art 2, ... using a layout with different subsequent subsummary's (subsummary on art 1, subsummary on size art 1, subsummary on art 2, subsummary on size art 2, ...)

I get :

art 1

size S art 1 : 30 units

size M art 2 : 20 units

size L art 2 : 15 units

art 2

size M art 1 : 30 units

size XL art 2 : 15 units

...

instead of the desired :

art 1

size S art 1 : 40 units

size M art 1 : 30 units

size L art 1 : 35 units

art 2

size S art 2 : ....

Why does this happen ? I can solve the problem by scripting a sort order and a layout for each (of the 30) items, but isn't there a way to get the desired result in 1 layout with 1 sorting command ?

Sorry if this question is too obvious, but I'm really stuck ... Thanks for helping me out !

[Oldfogey]

No, your question is not '...too obvious..' because I can't work out what you are actually doing.

Do you have just two fields - Article and Size? You should then have 30 records.

Sort on ArticleSize.

Your layout should have two sub-summaries - one by Size and the one by Article.

[jst]

Paul,

Thank for responding. Let me clarify the problem. The sorting problem occurs when I sort the way you describe : sort on article & size article in a layout with two sub summaries : article and size article. When doing this, I get a result like :

Article 1

article 1 Size Large : 40 units

article 2 Size 40 : 15 units

article 2 Size 50 : 25 units

Article 2

article 1 Size Medium : 30 units

Article 2 Size 40 : 10 units

Article 3

Article 1 size small : 15 units

Article 2 size 40 : 5 units

Article 2 size 50 : 10 units

Whilst I would expect (and like) :

Article 1

Article 1 size large : 40 units

Article 1 size Medium : 30 units

Article 1 size Small : 15 units

Article 2

Article 2 size 40 : 30 units

Article 2 size 50 : 35 units

The concept of the global db is larger : it calculates the totals for the ordered goods (30 articles) which works ok, but I fail to get a decent overview of the units of the goods, sorted by article and size.

[-Queue-]

Is there any way you could attach a sample file that demonstrates the problem? I can't determine just from looking at your data why the sort is skewed.

[jst]

Included is a sample of my problem

I hope this clarifies ...

tryout resumes.zip

[dbruggmann]

What kind of document is your file? Its name is

[Oldfogey]

I can't open your file either. My PC (W2K) says it's a funny file.

My guess is user error - that's what I tell my clients!

Are you sure you've defined the sub-summary parts correctly and that the sorts match the sub-summaries?

What you are getting looks as though you have the sort and the sub-summaries around the wrong way.

Your sort should be 1. Article 2. Size

Your layout should go Header/sub-summ Article Before [for the heading Article n] /sub-summ Size After. I presume you are not showing the detail records.

What are you using for Sizes? Numbers or text?

Do the sort and check the result in a layout that shows all records and see if you get what you want. Better still, put the individual records in (a copy of) your layout and see what you get.

[-Queue-]

Add .fp5 to the document name.

You have two actual fields, article 1 and article 2, when you only want one article field with 1, 2, 3, etc. entered. The same goes for your article size. You only need one field with one record for each value. If you need more than one article per record, try incorporating a related file where each record in the main one could have an infinite number of related articles. Then do your reporting in the related file.

[Lee Smith]

Hi Queue,

Thanks for confirming that it was a .fp5 file, I had tried that and FileMaker rejected the file. After your post, I went a little deeper with it by changing the File Type and File Creator using the Get Info in the Finder, and it now opens.

HTH

Lee

[jst]

Can you elaborate on this ?

I do need more than one article per record (=person), in fact approx 30 articles with specific sizes for that record (=the specific size for that person). How can reporting than be done in the related article db, when I need a summary of the articles sorted by article and size per class/school/school year/...

[-Queue-]

Look into relational databases. You could have one person record related to multiple article records, with the ability to sort by related fields as well as local ones. Do a search on this site for relational basics or fundamentals. Once you are comfortable with how they work, you will have a better idea how to organize your data.