7 Replies Latest reply on May 6, 2014 5:39 AM by philmodjunk

    Extended value list from multiple tables



      Extended value list from multiple tables



           I am working on a database of articles which have different types of related documents, in this case they are separated in two tables for document and drawings. I've created the relationship between the article and the document/drawing through a linking lineitem register and I show these lineitems records in a portal in the article-layout. It is also through this portal I create the lineitem records.

           As a validation, to only allow creation of lineitems on documents/drawings that already exist, I wanted to use 'Require member of value list'. I want to create this value list with the document IDs from the documents table AND the drawing IDs from the drawings table.

           Is there any way I can create such an extended table? I've only encouterd filtering and conditional lists so far but not how to extend the list.

           Greatful for all kind of response.

           Best regards, Emelie


        • 1. Re: Extended value list from multiple tables

               You'd need to merge the two tables--such as using Import Records to copy the data from one table into the other or from both tables into a third table.

               There might be a way to do this from a single record with a calculation field using ExecuteSQL and the UNION keyword, but I'm not sure how well that would work due to the fact that you'd need to both keep this a stored, indexed field--to serve as the source of values for your value list, and yet automatically update as records are added/removed/changed in the document and drawing tables.

               Why do you have this data in two different tables? What problems does that solve for you? (Maybe there's a way to get a unified table set up that stores the data for drawings and documents in a single table.)

          • 2. Re: Extended value list from multiple tables

                 On the other hand, if all you need this value list for is to validate the data, there are validation calculations that you can set up that do not need a unified table. A validation calculation, for example could check to see if the value is a member of value list 1 OR a member of value list 2 in order to confirm that it is a link to an existing record in one of the two tables.

            • 3. Re: Extended value list from multiple tables

                   Thank you for such a quick reply!

                   Ok, so no it doesn't seem to be any quick fix as I had hoped for =)

                   I have definetley considered putting all documents and drawings toghether in the same table, but it has been prefered to keep them separate so far since the document types are handled quite differently. There are different kinds of information provided, different type of users will have access to them, serial numbers will need to be kept to different series etc. I wasn't really sure how far you could stretch that kind of difference with only layouts, conditional calclulations and sorting. Do you think it is managable?

                   Best regards, Emelie


              • 4. Re: Extended value list from multiple tables

                     Oh, yes it's that type of thing I'm after! =)

                     I didn't know that you could check lists in calculations. What type of fuction could I use for that?



                • 5. Re: Extended value list from multiple tables

                       It's very manageable and you have more than one approach to consider. Keep in mind that you can set up as many layouts as you need based on the same table. Thus, putting both documents and drawings data in the same table does not keep you from setting up a layout just for documents and a layout just for drawings.

                       Option 1: This is the simplest method for handling this common database issue. Flatten your data model slightly by replacing your two tables with a single table. Just combine all the needed fields from both separate tables into a single table. When working with a record for a drawing, leave empty the fields that are only used for a document and vice versa. This is often the best choice if you only have a few fields that are specific to one record type or the other.

                       Option 2: Keep your tables separate, but add a third table with links to the other two. Move any fields and data from the original two tables that are common to both types of data into this added table. Use a pair of match fields to match to the original two tables that are now your "detail" tables. Leave the match field for a document record empty when the record in this central table is for a drawing and vice versa.

                       Your value list would then be defined to specify data from this added central table where you have combined the data common to both original tables.

                  • 6. Re: Extended value list from multiple tables

                         I got it to work with the comparing calculation as you suggested finally. Thank you!

                         It was the FilterValue function that did the trick. I didn't make the connection that I could have it working on a ValueList since it is stated as a text-function.

                    • 7. Re: Extended value list from multiple tables

                           IsEmpty ( FilterValues ( valueListItems ( ...