Jump to content
Salesforce and other SMB Solutions are coming soon. ×

one to many - import data

bart brichau

Recommended Posts


I have a relational database in which a master is linked to a related file, which in turn is master for a related file. The first master is also related to the third file.

The first master file is a file containing people who will be assessed for performance

the second file (related to the first and master for the third) is a file containing assessors

the third file (related to the first and second file) contains the assessment on the people of the first file, executed by the assessors in the second file.

I created a portal in the first file that shows the records of the third file

whenever I want to copy the results from the third file into the first file, Filemaker only returns the values of the first record.


John doe (person from first file) has been assessed by Mary and by Paul (their assessments are in the third file)

when looking at the portal in the first file I see both records for both assessments from Mary and Paul.

When I execute the script to import the data from the third file into the first file (using SetField), FM always returns the values for the first record (Mary's results) even though I ask for Paul's

why ? what to do ?



Link to comment
Share on other sites

Hi Bart,

From what you say, it would seem that the relationship in which your portal to the assessments file is based is driven by a match between a person ID in both files.


There are several ways to approach the problem you are experiencing. However in order to separately address the various assessment records using the Set Field [ ] command, you will need a relationship that enables you to isolate individual assessments.


An example of a method for achieving this is:


1. Create a serial number field in the assessments file.


2. Create a calculation field (of result type text) called 'AssessmentID' in the assessments file with the formula:


PersonID & "|" & Serial#


3. Create a new global text field called 'gAssessmentID' in the persons file.


4. Create a new (additional) relationship between the persons file and the assessments file. Call it 'CurrentAssessment' and base it on a match between the gAssessmentID field and the AssessmentID field.


5. Create a value list in your persons file which uses 'Only related values' from the AssessmentID field in the assessments file, via the original (portal) relationship to the assessments file. Call the value list 'RelatedAssessments'.


With the above in place, you will then be able to access a particular assessment by using a script with two steps, along the following lines:


Set Field ["gAssessmentID", "MiddleWords(ValueListItems(Status(CurrentFileName), "RelatedAssessments"), (3 * 2) - 1, 1)"]

Set Field ["AssessmentText", "CurrentAssessment::AssessmentText"]


The first Set field set in the above example, will extract the third assessment key value for the current person (as specified by the number at the start of the second last parameter in the Set Field formula) and place it in the global gAssessmentID field, thus creating a temporary relationship to that specific record. By altering the second last parameter, you will be able to access any available assessment for a given person.


The subsequent step is then able to source data from the record which has been specifically isolated by the preceding step.

Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use