Is there a way to concatenate two value lists?

[Sharon]

Hello, 

I have two tables, both have a field "name". Let's call them the "dads" table and the "moms" table. 

In a third table (call it the "children" table)  I want to have a field ("parent")  where the user can choose a name using a drop down. The values in the dropdown should be all available names of dads and moms. 

I cannot find a way to concatenate the lists. I can build a concatenated list in a calculated field but I cannot find a way to use that list in a dropdown.

Is there a solution?

[comment]

I think the simple solution would be to have a single table of Parents, with a field distinguishing the "dads" from the "moms" (assuming such thing is even allowed nowadays...).

 

[LaRetta]

Hi Sharon,

It is far simpler to have a single table of 'like items' (Parents, Names, People, Dogs, whatever) and then use techniques such as Finds, relationships or portal filters to isolate records (by Gender?) as you need them, rather than attempting to 'put two tables back together'. 

17 minutes ago, Sharon said:

The values in the dropdown should be all available names of dads and moms.

 You have identified the issue yourself and it is a common misstep in database design.  If you can, I suggest you change it now because you will continue to run into this problem throughout the file's life.  If you absolutely can't combine them now, there are techniques for solving your problem but best to fix it from the start. 🙂

[Sharon]

Thank for the responds. Well, the "moms and dads" naming was just for the sake of simplifying. In reality, the tables have very little in common. They represent two completely different entities and it's just this one incident where it would be very handy if I can list them in one list.   

LaRetta, I would love to hear more about the techniques you've mentioned...

 

[comment]

20 minutes ago, Sharon said:

They represent two completely different entities

I am not sure I would agree with this statement. The fact that you need to select from a list combining both of them suggests they ARE in fact a single entity, at least for some purposes. But of course, you tied our hands in this discussion by using an example that does not fully represent your true situation.

In general, in a situation like this when you have 2 (or more) different subtypes forming a single supertype, there are 2 possible solutions:

1. A single "wide" table having all possible fields. This has the advantage of being easy to implement;
2. A single table having the fields that are common to all subtypes, and 2 (or more) subtables with fields that are specific to each subtype (related one-to-one with the supertype table) . This may be the more "correct" solution, but it is not as easy to implement in Filemaker.

 

Edited August 14, 2023 by comment

[LaRetta]

I agree completely with Comment, Sharon.  Please provide your real table names and more information or else we won't be able to provide the best solution for you.  If you can, it would help to see a picture of your graph.

[comment]

Also please update your profile to reflect your version and OS, so that we know what you can use.

 

[Sharon]

I've attached my graph. The two tables are Locus and Feature. As you ca see, Locus is a much more elaborated entity. Also, the naming (the pk) of the records in them is different. In the Locus table I have a field that should hold a name of either a Locus or a Feature in the same area. 

[LaRetta]

Since you are using IDs, Sharon, this is what I would do (added benefit of filtering Area portals, searching by this Type selected field, sub-summarizing, filtering etc in future):

1. Add a field (text) to your Area table called TypeSelected (or something similar which makes sense to you).
2. Create a value list called Type (with either the text Locus or Feature)
3. Place it on your layout (see my example file)

Nice graph! 😺

 

I see you want the selection in the Locus table instead of Area - that is fine.  The solution uses value lists of each table and the resulting Select field holding the selected ID can be anywhere you wish.  Same with the Type field.

SelectValues2.fmp12

Edited August 14, 2023 by LaRetta

[Sharon]

Yes! That could work. Thank you! It's even better than a concatenated list since it won't be so long. Great

 

[LaRetta]

Sharon, I changed the attachment which includes a trigger to clear the Selection field if User changes the Type so it doesn't display that ugly UUID.  You haven't indicated whether you use the name as an ID or a unique UUID.  It might bite you in future if you've used names as IDs.  I suspect, by the looks of your graph, that you are using meaningless IDs - let us know if otherwise so we can help further.  🙂

Also, please remember to change your profile indicating your OS and FM version, as Michael requested.  It'll help us help you in the future!