Discussions

Creating a relationship that selects the proper record in a parent table based on date and the selection made from a different parent table.

  • 1.  Creating a relationship that selects the proper record in a parent table based on date and the selection made from a different parent table.

    Posted 04-22-2018 14:22
    I'm having trouble creating the proper relationship. The following are the tables and fields involved when we're running into trouble:

    Classes - Class Name
    Rates - Effective date, Rate 1, Rate 2, etc.....
    Members - Member Name
    Membership Records (this contains records of members being assigned to classes and if it's easier, we could assign rates to them as well)- Effective Date
    Remittances - From Date, To Date, Hours

    In the remittance table, we'd like the user to pick the member and then based on the dates of the remittance and the member selected, have the proper Membership Record populate which would be determined by the effective date in the Membership Records for the related member that was selected.  Below is a simple example of the relationship.

    Also, our hope is to be able to import the data into the remittance table once the relationship is set-up.