Create relationship RRS feed

  • Question

  • I have two tables that I need to establish a relationship between.

    The primary table is Account Rep and it contains 3 fields in the key... Client is the first field and Account is the second. The third field of the key is named PreFix and is a simple sequence number to be used in the event there are multiple combinations of Client/Account in the table.

    The second table is the Workorder table. It contains the Client and the Account but there is no PreFix (sequence number).

    When I attempt to creat the relationship from Workorder to AccRep I select Client in both the WorkOrder table and the AccRep table and I also select Account in both tables but the Primary side of the selection window wants an equivalent field to PreFix (sequence) to be selected on the Workorder side when there is no sequence number in the Workorder table. 

    If I remove the PreFix field from primary side I get the following error message: The relationship must be mapped on the key fields and only the key fields of the primary side of the relationship. (see image one below)

    If I add an additional field called PreFix to the WorkOrder table and then try to create the relationship, I get the following error message: The multiplicity is not compatible with the required state of the shared fields. (see image two below)

    What am I missing? What do I need to do to create a relationship between the records in the WorkOrder table based on Client and Account and the AccountRep table which also contains Client and Account.

    Thursday, December 6, 2012 10:28 PM


  • Never mind... I figured it out.

    It appears that not only must you use the key fields on the primary side of the relationship (which by definition makes them required fields)... but the corresponding field on the foreign side must also be required. The reason the relationship I was trying to setup wasn't working is because the field I created on the WorkOrder side to correspond to the PreFix field in the AccRep table was setup as "Allow Nulls", but it needed to be a "required" field.

    Now for the second half of my problem. Does anybody have any idea what needs to be done to force the Modal Window Picker to only display records for a particular set of records from the AcctRep table?  Rather than have the user scroll through hundreds of records, I only want the records to display that match the Client and Account that were already selected on the screen.





    • Marked as answer by Angie Xu Thursday, December 20, 2012 1:21 AM
    Friday, December 7, 2012 2:11 AM