1 Reply Latest reply on Nov 1, 2010 1:59 PM by philmodjunk

    Relational DB - 3 fields matched to 1 field issue



      Relational DB - 3 fields matched to 1 field issue


      This is for tracking back-ups:

      I have a table of file folders, and a table of discs.

      Each file folder can be on up to 3 discs, so in the file folder table I have 3 drop-down fields to pick a disc name from the disc table.

      I've linked up these 3 fields to the disc name field of the other table in Relationships, but it's using an 'AND' connection for each instead of keeping them separate (or maybe if there's a way to use an 'OR' relationship it might solve things too)

      The most important issue is that I can see what discs a folder is on because that's how i am entering the data, BUT not sure how to do the reverse of using the disc table to list all folders that are on each disc. 

      A portal is only seeing items where all 3 drop-downs contain the same disc name, since it has that 'AND' between each relationship so all 3 have to be equal (or another operator, but none seem helpful, it's the 'AND' that seems to be the problem).

      Thanks for any help

        • 1. Re: Relational DB - 3 fields matched to 1 field issue

          Use a single repeating field with at least 3 repetitions and use it in your relationship to get the "OR" based relationship you want.

          You can also use a single text field and separate the three values in the field with returns such as this calculation field set to return text:

          List ( DiscField1 ; DiscField2 ; DiscField3 )

          Either approach will set up the "OR" relationship you want.

          A third option is use use a Join table to link your records, but this may not be needed for this situation.