Data on one relationship, sorts on another

[Esmo2000]

Hi everyone,

Wondering if you could offer some help to my woes! I currently have a relationship which is defined on an index number of records. This is a one to many relationship. Basically, for any record, you could enter any number of associated payments and dates. For any record,the associated records will have another number (not unique to the record but consistent across all of its associated records).

I have two questions regarding this scenario:

Firstly, how can I make it such that this second non unique number is forced to be consistent across all of the related records?

Secondly (and significantly more importantly!), I wish to take all of the records from the associated database, pull up all records that have some specific number (the second one, not the first) sort them by date, and place them all on one screen.

Because i am so poor at explaining, here is an example!

Database 1:

2 - John

3 - James

4 - Jarold

Database 2:

2 - Payment 1: 500$ 10/12/2004

Payment 2: 400$ 11/12/2004

3 - Payment 1: 500$ 1/1/2003

Payment 2: 600$ 11/5/2004

Payment 3: 200$ 12/12/2004

4 - Payment 1: 500$ 9/12/2004

When presented with the sorted results I see:

3 - Payment 1: 500$ 1/1/2003

3 - Payment 2: 600$ 11/5/2004

4 - Payment 1: 500$ 9/12/2004

2 - Payment 1: 500$ 10/12/2004

2 - Payment 2: 400$ 11/12/2004

3 - Payment 3: 200$ 12/12/2004

[LaRetta]

Esmo2000 asked...how can I make it such that this second non unique number is forced to be consistent across all of the related records?

To ensure consistency in the number, use a Value List. It sounds like your data is already in place, so you may have some work to do to get it into shape. You can view all the numbers now (one of each number) by placing your cursor in the number field (in your related file) and [CTRL-I]. This will tell you what numbers need to be corrected. After your numbers are correct,create your Value List (in this related file). Base your Value List on 'values from field' and select this number field.

I assume you want Users to select the number from within your Main db? If so, now create a Value List in your Main db but this time, select 'Use Value List from another file', select this related file and select your newly created Value List. Attach this Value List to the field as popup for your Users to select from. You can even validate the field (through Define Fields > Options > Validation and select 'by Value List' to stop incorrect entries in the future.

I am unsure what this non-unique number represents to you. If it is not a number selected during entry but rather you want a number generated, you will need to explain a bit more about its purpose.

I wish to take all of the records from the associated database, pull up all records that have some specific number (the second one, not the first) sort them by date, and place them all on one screen.

If you wish to view this data from your Main db, you will need two scripts. Your script in Main will call the script in your related file. Your related file script will find the requested records, sort them and then switch to a report view. Users can then preview (or print) this report. When done, they will be switched back to the Main db. The User won't know they are changing files - it will appear as though they are still in the Main db.

Without knowing your specific structure, here is the generic idea. You will have to adjust it as needed. First, create your report in your related file. Layout > New Layout > Columnar/List. Select Report with Grouped data (and select subtotals and grand totals if you wish). Select your fields for the report - be sure to include this number field if you wish to have the data categorized by number. Organize by Category and select your number field. Sort by your number field (and any additional field(s) you wish), like your payment date. Continue through the report design until you come to 'Create a Script.'

Let FM create the script for you which will include: Go To (this) Report layout, sort your records by number and display your report. If the records aren't sorted by your Category (number), the report will not display correctly. Name the script NUMBER REPORT. You can name them anything you wish, but it is easier to identify what I mean in the following script if I have a name. Change as you need it.

Then you need a Find to isolate only the records you want for your report. Create a layout (FIND) and place the fields you want Users to use to search, ie, your number field and date (or other criteria). You can attach your Value List to this number field so Users won't try to find an invalid number.

At this point, FM has created your script so all you need to do is modify it so it looks like the following:

NUMBER REPORT:

Set Error Capture [ On ]

Allow User Abort [ On ] .... turn this to OFF after you are sure your report is perfect

Loop

__ Go to Layout [ FIND ]

__ Enter Find Mode [ Pause ]

__ Perform Find [ Replace Found Set ]

__ Exit Loop If [ Status(CurrentFoundCount) ]

__ If [ not Status(CurrentFoundCount) ]

____ Show Message [ Buttons: YES, NO; "No records found. Try again? ]

____ If [ Status(CurrentMessageChoice) = 2 ]

______ Show All Records

______ Exit Script

____ End If

__ End If

End Loop

Go to Layout [ NUMBER REPORT ]

Sort [ Sort Order: Number (Ascending) ] [ Restore sort order, No dialog ]

Enter Preview Mode [ Pause ] ... you can add a print process here also

Enter Browse Mode [ ]

Show All Records

IMPORTANT: When you exit the script, it will ask if you want to keep or replace the sort order and the find. Select KEEP on the sort order.

Your report will look like:

2 - John

___10/15/2003 400$

___11/16/2003 500$

________Total for John 900$

3 - James

...... etc.

Now for the script in your Main db:

GENERATE REPORT:

Freeze Window

Perform Script [ subscript External, "relatedfile.fp5" ]..... select your newly created NUMBER REPORT script ...

Refresh Window [ Bring to Front ]

There are many variations on this process. Which is best for you will depend upon your specific needs and others may have suggestions also. But this will get you going.

LaRetta

[Esmo2000]

Thank you SO much! This is such a fantastic help! I will have a go at this right away! Thanks again!

Jim