Removing dupes in a report

[Rich S]

Howdy, howdy. Don't get up.

I'm overlooking something probably very obvious--no surprise there! Referring to the attached file, I want to prevent duplicate Federal Aviation Regulations (FAR) from appearing in the report--in the example attached, FAR 121.191. I thought of a sub-summary angle, but alas no success since it won't allow for column set-up in there. The nerve!

I appreciate your help in solving this minor brain hernia.

Many thanks!

Rich

FAA_Database.zip

[John Mark Osborne]

If you can forget about the columns, you are right about the report being very easy to create by adding another subsummary part. Subsummary parts can remove duplicates in a report. What you don't say is what the rest of the report will look like. For example, will there be additional Manual categories other than CRM (I am guessing yes)? If so, how many possible columns could you have in the report for any one Manual category? Will there be any totals or counts or just a listing of all the Manual categories followed by the Far_Decimalized values? Maybe you can create an example of the report in a spreadsheet, take a screen shot and attach it to this post so we can get a really good idea what you want.

[John Mark Osborne]

The reason I ask all these questions is because it makes a difference on which solution I offer you. Basically, you are asking to create a cross-tab report which FileMaker is not very good at doing. There are many solutions out there so any help you can provide in outlining the details of how your report will look will really help in the quality of the answer.

[Rich S]

Hi, John--good to hear from you again and thanks for your help.

Attached is a .jpg of what the report should look like. As you can see, the FARs will increment from left to right...though if it'll make things easier they can be listed top-down.

Cheers!

Rich

[John Mark Osborne]

It will make things quite a bit easier if you list your FARs vertically rather than in columns. Then, all you have to do is change your Body part into a Subsummary when sorted by Far_Decimalized.

However, since you don't have any summary fields, you could easily eliminate duplicates from your found set using a looping script. This would allow you to keep the report in it's current configuration. Here's the script:

Freeze Window

Sort Records [Restore; No dialog]

Go to Record/Request/Page [First]

Loop

Go to Record/Request/Page [Next; Exit after last]

If [***see below for formula***]

Omit Record

Go to Record/Request/Previous

End If

End Loop

The calculation in the If script step is a little long so I separated it:

GetNthRecord(MANUAL Data Entry::Manual; Get(RecordNumber) - 1) &

GetNthRecord(MANUAL Data Entry::Far_Decimalized; Get(RecordNumber) - 1)

=

MANUAL Data Entry::Manual & MANUAL Data Entry::Far_Decimalized

I have also attached a modified version of the file so you can just download it.

FAR-Manual_Database_Copy.fp7.zip

[Rich S]

Wow--very cool! Thank you! I never would've constructed the script you came up with; I'll have to pour over it for a while to see the logic behind it.

As I've said before, some enterprising soul should gather all the fixes together in a website like this, organize and categorize them, then publish it in a volume called "Fabulous Fixes for the FileMaker-Challenged"--I'd buy a copy in a heartbeat.

Thanks again,

Rich