1 Reply Latest reply on Apr 22, 2011 9:37 AM by philmodjunk

    Relating Fields



      Relating Fields


      I have a database that is for furniture warranty claims. When someone calls in to create a claim, sometimes they have more than one piece they want to have repaired. So in the cases table, I have the furniture item code fields 1-4 so that a customer can make a claim for up to 4 pieces at a time. So for each item code there is a Problem Type field 1-4 (explaining the nature of the problem for the corresponding item code) and a Problem Detail Field 1-4 (giving details of the problem for the corresponding item code).

      My problem is that I would like to be able to make reports and do cross-reference searches for certain item codes with certain problem types/details but I don't want them to mix up item code 1 with problem type 3 for instance. Is there a way to tie the Problem Type 1 and Problem Detail 1 fields to the Item Code 1 field and so on? Is there some way I should be structuring it differently instead?

      Any help is greatly appreciated.

        • 1. Re: Relating Fields

          Best approach is to change the design of your database so that you don't have 4 different fields for recording the same type of data. Use a related table of records where one record in this table records a claim for just one item of furniture for a given customer. You can use a portal to this table on your Customer layout to record claims for different items of furniture, but for the same customer.

          Not only does this enable you to create a report layout based on this new related table where you can do your cross-reference searches, but you will no longer be limited to just 4 items of furniture.