Report order and table basis

[benwiggy]

I have a table A, which has multiple items for each record in a sub-table B, which itself has multiple items in each record for a sub-table C.

The records in C are all from a Value List.

I want to create a report that sorts by C first, then A, then B, like this:

Item from Value List C

------> Record from A

------> ------> Multiple records from B

------> Record from A

------> ------> Multiple records from B

Next Item from Value List C

Now I've tried using the Wizard, but it doesn't completely work. I get items from B in one group that belong to more than one A, without getting a new record listed for A.

On which of the tables should the report be based?

Do I need 3 report categories, or just two?

Sorry if this is basic, but I've tried several variations, and I'm just not getting what I need.

[comment]

Could you please rewrite your question using the terms Parent, Child and Grandchild instead of A, B, and C? I tried to do the substitution myself, but the result doesn't make sense.

[benwiggy]

Err, if you think that's less confusing, ok! (Essentially A is Parent; B is Child; C is Grandchild.)

I'm trying to get a report like this:

Item from Value List in Grandchild

------> Record from Parent

------> ------> Multiple records from Child



------> Record from Parent

------> ------> Multiple records from Child



Next Item from Value List Grandchild

The closest result I'm getting at the moment gives me Child records from more than one Parent record mixed up together.

[comment]

I'm trying to get a report like this:

Item from Value List in Grandchild

------> Record from Parent

------> ------> Multiple records from Child

That doesn't make sense. A child has many grandchildren. Which Grandchild value should group together several parents?

[benwiggy]

Perhaps I've misunderstood what Grandchild etc mean.

Table A is musical compositions.

Table B lists movements or sections within those works.

Table C lists characteristics of each section in B, and the bar number where that characteristic occurs. The same characteristic can appear several times in one section.

I then want to create a report that shows something like this:

Chromaticism: (from C)

-----> Mozart Piano Concerto No 1 (from A)

------> --------> 1st movement, bar 53 (from B )

------> --------> 1st movement, bar 99 (from B )

-----> Bach Christmas Oratorio (from A)

------> --------> Opening section, bar 23 (from B )

Discords (from C)

------> Chopin (from A)

etc, etc.

Does that make it clearer?

By basing the report on the Grandchild (Table C), I get all the results I want. However, for some parts of the report, I get a continuous list of sections from Table B that relate to more than one record in A. But other parts, it does seem to work.

In fact, it seems like every other record in A is displayed for each record of C, even though the records in B that relate to the missing record of A are displayed.

[comment]

OK, I think I 'm starting to understand this. It seems like your sort order is incorrect. Why does your example place Mozart Piano Concerto No 1 before Bach Christmas Oratorio?

[benwiggy]

That was an example that I made up just for the purposes of explanation, but in fact you're on to something as the sort order is not alphabetical, or perhaps reversed.

On top of that, what I'm getting at the moment is:

Chromaticism:

-----> Mozart Piano Concerto No 1

------> --------> 1st movement, bar 53

------> --------> 1st movement, bar 99

------> --------> Opening section, bar 23 (i.e. no mention that this is Bach)

But I will get a third composition in the same characteristic declare itself below that. The fourth is similarly absent.

In the script that sorts the Report (created automatically by the Wizard), the three fields are all in ascending order, and they are:

Characteristic (from C ),

then a Name field from A (complete title as one field, using List() to put it together from separate fields)

then Movements Name from Table B.

[comment]

Your records should be sorted by:

C::Characteristic

A::Name

B::Name

C::Bar

[benwiggy]

I've fixed it.

Filemaker didn't like something about the A::Name field. I created it for this report, as a concatenation of other fields, using Substitute (List (Title; Subtitle); ¶; " ")

But the calculation was being saved as a number, even though it appeared as text. Changing the result to text fixed it.

Thanks for pointing me in the right direction.