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.)
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.
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
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?
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.
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.
IsEmpty ( FilterValues ( valueListItems ( ...