Numbering a list of related records and restarting that numbering in list view

[Daniel S]

So I have 2 tables. Inspection and Violation. They are joined in a many-to-many relationship.

There are 2 records in Inspection. There are 6 records in Violation.

I have a layout set up with parts (body, sub-summary) that show both inspection reports (records) and the list of violations on each inspection report.

So there are 2 pages when this layout is in browse mode with the 2 inspection records- one inspection report on each page and a list of the violations within each inspection report.

My problem is the numbering of the violations. Right now, the first inspection report violations are numbered fine (1, 2, 3, 4). The second inspection has violations numbered 5, 6, 7, 8. Because this is another inspection, the numbering should start over (1, 2, 3, 4). I am using the @@ operator to number the records.

Thanks for any thoughts :)

[dsghs]

To simplify:

I need to create a field that will serialize the records with the same value in the category field.

IE, serializing all records where Field 1 = 1, then 2, etc...

Field 1--- Field 2 -- Serial

1 --------- 2 --------- 1

1 --------- 3 --------- 2

1 --------- 1 --------- 3

2 --------- 7 --------- 1

2 --------- 1 --------- 2

2 --------- 4 --------- 3

2 --------- 5 --------- 4

So how can I generate and update that serial field?

[dsghs]

Well, I found that if I use the @@ operator in a portal, the numbering starts over every time there is a new portal.

[comment]

You can also use a summary field defined as running count with "Restart summary for each sorted group". This way you can report from the child table, without using portals.

[dsghs]

You can also use a summary field defined as running count with "Restart summary for each sorted group". This way you can report from the child table, without using portals.

I tried that, but I need to list multiple records related to multiple records using two tables joined in a many-to many relationship. I can only base the layout off of one table which means it will only show multiple records from that table and only the first related record from the other table that the layout isn't base on.

I'll have to make a separate two-page layout with portals but I'd have to do that even if I listed with sub-summary part since they are printed report pages that have to be formatted differently when there are two pages... so setting a finite number of displayed records in a portal will work for me.

[comment]

I tried that, but I need to list multiple records related to multiple records using two tables joined in a many-to many relationship.

Perhaps that's where the real problem lies. Having "two tables joined in a many-to many relationship" is possible - but it places significant limitations on the types of reports that can be produced. In most cases, a many-to many relationship needs to be resolved into two one-to-many relationships, using a join table.

[dsghs]

Perhaps that's where the real problem lies. Having "two tables joined in a many-to many relationship" is possible - but it places significant limitations on the types of reports that can be produced. In most cases, a many-to many relationship needs to be resolved into two one-to-many relationships, using a join table.

Yes. I'm sorry if I was unclear. There are three tables - (Inspection) (Violation) and (InspectionViolation) - the last being the join table creating the overall many-to-many relationship.

[comment]

Well, then if you report from the InspectionViolation table, with records sorted by InspectionID and by ViolationID, and restart the count for each sorted group when sorted by InspectionID ...

[dsghs]

That's the thing - when I report from the join table, I can't get more than one Violation to show up per Inspection. It shows all the inspections in list mode, but just the first (if any) violations.

[comment]

when I report from the join table, I can't get more than one Violation to show up per Inspection.

There is no reason why you couldn't - so perhaps something is wrong with your implementation.

See also:

http://www.fmforums.com/forum/showpost.php?post/246136/

[dsghs]

Please identify "something" - I don't see how I could have implemented it wrong. Perhaps you do not understand that I am trying to accomplish. Either way, the portal is actually the better route for me since I'd like to limit the number of records on a given layout.

[comment]

Please identify "something" - I don't see how I could have implemented it wrong.

It's rather difficult to diagnose a file you cannot see... the only thing I can say for sure is that with the correct implementation it should be very easy to produce a report based on the join table in the form of:

Inspection 1:

1. Violation A

2. Violation B

3. Violation D

Inspection 2:

1. Violation C

2. Violation D

Inspection 3:

1. Violation A

2. Violation B

3. Violation C

4. Violation D

etc.

If that's not what you want, then indeed I do not understand what you are trying to accomplish.