Repeating Fields as Match Fields in Relationships - Query

[Tony H]

Greetings. My query concerns working with repeating fields in relationships.

Table 1 (Client) has the following non-repeating field: Company Name. Table 2(Projects) features two non-repeating fields: Project and Client. The match field between Table 1 and Table 2 is as follows: Projects (Client) = Client (Company Name). The goal of Table 1 is to add projects to the single client record (through a set field option in a portal on the Client layout) so that all of a client's projects can be managed from a single record.

Table 3 (Timecards) features the following repeating fields: Project (repeats 10 times) and Client (also repeats 10 times). Table 3 tracks billable hours spent on a project. The field Project in Table 3 features a drop-down list based on a value list of projects drawn from Table 2. The goal is to be able to ensure that when a user working with the repeating field Projects in Table 3 assigns a project to any of the 10 repeating fields, the field Client will show a matching client record based on a relationship with Table 2 (assuming this is the correct way to structure that goal).

Currently, the match field between Table 3 and Table 2 is as follows:

Projects (Project) = Timecards (Projects). As many of you have already concluded by now, if you have Table 3's repeating fields Project and Client side by side on a layout, the current relationship or match field between Table 3 and Table 2 only produces a match field in the FIRST of the repeating fields where there is a relationship between Table 3 and Table 2.

Is there any way of structuring the relationship between Table 3 and Table 2 so that the matching client info shows up in ALL of the Client fields based on choices made in the Project field? Thanks so much for considering this query.

Tony

[RalphL]

First I would recommend that you add ClientID, ProjectID, TimecardID to your tables and use these as you key fields in the relationships.

Next I recommend that you rethink the use of repeating fields. A single record for each charge would be the proper way to handle this. In Project you can see all the timecards in a portal and you can get the total charges. In Client you can see the Projects and there total charges in a portal.

[Tony H]

Thanks, Ralph.