Table View & Value List problem: I need to display "values from first field" instead of "values from second field"

[dmontano]

Hi all,

In FORM view

I created some pop-up value lists assigned to fields defined as foreign key fields to related records from another table. The value lists are defined as using the "id" of the first field, and displaying the second field "name". Nothing unusual with this and works as intended.

In TABLE view, the field functions just as it does in FORM view. This is fine for "data-entry" and functions as one would expect.

However, there are times when I wish to see the values from the first field in TABLE view, and not the "display values from second field".

How can I achieve this?

I have poked around to see if the might be a configuration option in TABLE view, but I can't find anything.

I have duplicated the field in FORM view, removed the pop-up value list, changed to an "Edit" field to only show the "id" - which correctly displays the "id" in FORM view, but switching to Table view the field ONLY shows the Pop-up (probably because I have both "styled" fields on my form view (with and without popup). I am assuming if I remove the pop-up entirely from FORM view and leave the "Edit" style field - it would work.

Any help on this is appreciated.

[comment]

You can have several instances of a field on a layout, but when you switch to Table view, only one of them will be shown.

Which one? Filemaker Help does not say (at least I haven't been able to find it). A simple experiment I performed leads me to think it's the one that is positioned closer to the top edge of the layout body part (or to the left edge, in case of a tie, or the one that's further to the back in case that are aligned both horizontally and vertically).

So try moving the edit-box version of the field upward so that it is above the one defined as a pop-up. If you want, you can move it to the right of the layout's right edge, so you won't see it in Form view.

 

Edited August 31, 2023 by comment

[dmontano]

Hi Comment,

Thank you for the reply. I tried as you suggested - with no luck. I am on 19.6.3.302.

I tried locating a duplicate of the "Pop-up Menu" field, configured the field as "Edit Box", in both the "Top Navigation" part and the "Body" part, and insured it was above the "Pop-up Menu" version, to the left of it, and even brought the field to the front in the stacking order: no luck. 

I even tried a duplicate of the "Pop-up Menu" configured as a "Drop-down List (with the intent of hiding later as you describe) with no luck.

I believe the issue may be a "Pop-up Menu" takes priority in the display behavior in Table view.

If I use a "Drop-down List", it DOES displays the "first-field" and not the "second-field" from a value list. While this achieves what I want in Table view, it is not the Control Style I want in Form view. In addition, I would like the ability in Table view to see either the "first" field or "second" field on an as-needed-basis.

I guess I could achieve what I want by creating two layouts: one for the User with Pop-up Menus and a second one for Developer showing just "Edit Box" control style fields so I can see the actual data stored in the field. I really don't want to create two layouts for every table just so I can view both the "first-field" data AND the "second-field" data.

Is there another way to achieve the ability to see both first and second field values in table view?

 

Edited August 30, 2023 by dmontano
Unnecessary thought

[comment]

35 minutes ago, dmontano said:

Is there another way to achieve the ability to see both first and second field values in table view?

That's not how I understood your original post. I thought you wanted to see only the Name (2nd value) while in Form view, and only the ID (1st value) while in Table view.

There is a very simple way you can see both in Table view: just add the related ID field to the layout. You'll probably want to make it non-enterable so that you don't modify it by accident.

 

Edited August 30, 2023 by comment

[dmontano]

I want to apologize: I struggle to explain these types of things and my communication skills have always hampered me.

You understood correctly. I "expanded" what I wanted the more I thought about this.

I realized that having the ability in Table view to interact with the Pop-up Menu just like in Form view would be good: Add a record, edit a record, modify a field value via the Pop-up Menu (just like in Form view) where I see the "Second" field value (Name) and not the "First field value (ID) is a good thing: basically function in a normal fashion. This portion works as expected.

The problem comes about when I try and "see" the FOREIGN KEY ID value stored in the table in which the Table view is based upon when a Pop-up Menu item was configured in Form view. The Pop-up Menu functionality carries over to the form view which prevents me from seeing or interacting with the values of the FOREIGN KEY ID.

If I use a "Drop-down List", I see the FOREIGN KEY ID, but not the value from the Second Field. I could add the related Second field, but I did not want to change from a Pop-up Menu to achieve this.

I have attached a stripped down file to share what I have. See the layout "Product Type" in table view. I can not figure out how to display and interact with the values stored in this field - while in table view:

product_type::product_type__product_type_category__id_fk 

product_database_dfa_copy.fmp12

Edited August 31, 2023 by dmontano
grammar

[comment]

I am afraid I am still confused regarding what do you actually want to see in each view.

Here's a simple demo where the pop-up menu field (showing Name only) is visible (and modifiable) in both views, while the ID value can be seen only in Table view.

(Hint: scroll to the right to see the field in Layout mode.)

views.fmp12

[dmontano]

Thanks Comment, I have downloaded your file.

What I surmise is this:

1. When using a Pop-up Menu item in Form view, where Pop-up Menu is configured with a Value List utilizing "Use values from first field" AND "also display values from second field" (FOR EXAMPLE: use ID first field, display NAME second field) - ONLY the second field will be displayed in Table view and there is no way to add another instance of this field (even as EDIT field on layout) to display first field values.

2. A Drop down List will show the first field values and not the second field. To show second field, you can simply add that related field by modifying Table view.

3. Creating an additional layout just to show the "raw" values stored in a table (foreign key values are what I am dealing with) does not make economic sense.

4. When I have a need to see in, Table view, the actual value STORED for foreign key fields in a table, the field configuration will have to be temporarily changed to either an EDIT Box, or a Drop down list.

[comment]

Not exactly.

If you have defined your value list to "Show values only from second field" then:

• a field formatted as a pop-up menu will always display only the 2nd field (Name, in you example) values - both when selecting and after selection;
• a field formatted as a drop-down list will display only the 2nd field values when selecting. After that it will display the selected value (i.e. the ID in your example).

This behavior is the same regardless of the current layout view. You will see a different behavior only if you deselect that option in the value list setup.

When you have the need to see, in Table view, the actual value stored in the foreign key field, you must add another instance of the field to the layout and format it either as an edit box or as a drop-down list. And you must arrange it so that the Table view will display this instance instead of the one formatted as a pop-up menu. You cannot show both.

Another option to view the same value as the one stored in the foreign key field is to place the related primary key field on the layout (that's what my demo above does).

Yet another option is to use 2 value lists - but I don't think you need to go that far.

 

Edited August 31, 2023 by comment

[dmontano]

11 hours ago, comment said:

And you must arrange it so that the Table view will display this instance instead of the one formatted as a pop-up menu. You cannot show both

I have discovered this behavior:

Table View will utilize the behavior of the FIRST instance of the fields configuration. If the field is first configured as a Pop-up - that is what Table view will present: no matter HOW you configure a second instance of the field or WHEREVER you place that second instance. You can change how that first field is configured at any time (change to Drop down List) and Table view will respond accordingly. Again, this is what I have experienced fiddling with this.

I have not been able to present, in Table view, both the "first field" value, and the "display second field value" at the same time by attempts of TWO field instances. The solution I have decided upon is when I need to see "first field", I will change the pop-over to a drop down list in Form view. This will show what I need in table view. And, as Comment suggested, I will place in Table view the field from the related table: this has not required I place the field and hide it in Form view, I am simply adding the field in the "MODIFY..." table view dialogue. This will allow me, in Table view, to COMPARE if the FOREIGN KEY matches the related PRIMARY KEY in the other table.

The reason I need to compare fields like this, is it sometimes occurs when Primary Keys are prefixed with words - and then the words are changed for some reason. I fully understand UUID and am not advocating one way or the other as to the choice to use those instead of prefixed IDs. What I am dealing with is the situation when receiving data from other sources where the keys are already provided and the prefix need to be changed due to conforming to a different schema. It is in these cases where I have found a want/need to see both the FOREIGN KEY value and the PRIMARY KEY value of a relation.

Using Comments suggestion, and fiddling with this a bit, I have found a workable solution. It would be nice to add two instances of a field and configure their display differently, and be able to view this in Table view.

What can be done instead is what I have settled in on doing, but it requires access to layout mode and the effort to toggle the Control Style between Pop up & Drop Down List.

I have attached screenshots to show the different views you can get in Table view, but requires choosing either Drop down or Pop up in Form view to achieve the display. IDEALLY, I would like to see all THREE bits of information in Table View:

1. The Foreign Key stored data (ID_FK) from first field

2. The related Display data (Name) from second field

3. The related Primary Key (ID_PK)

 

With Comments help, I understand a bit better, and have been able to come up with a way to work with the data and move forward. I suspect I still don't fully have a command understanding of this, but I see this as resolved in my head.

Thanks again Comment!

Edited August 31, 2023 by dmontano
Attempt at clarity

[comment]

 

25 minutes ago, dmontano said:

IDEALLY, I would like to see all THREE bits of information in Table View:

1. The Foreign Key stored data (ID_FK) from first field

2. The related Display data (Name) from second field

3. The related Primary Key (ID_PK)

See the attached file.

views2.fmp12

[dmontano]

Ok, this drove me bananas.

Your file shows what I wanted to do, so, I tried applying it just as you did: no luck.

I spent the last hour with this and finally got it to work. Perhaps you wanted me to figure out how you got it to work! lol

I "believe" the mechanism FileMaker uses to determine which field instance takes "display priority" in Table view when you have two instances of the same field is Tab Order. The field instance you wish to have display priority in Table view must be set lower than the other field instance.

For example: if the first-instance field on viewable layout has a Tab Order number of 4, the second-instance field in the non-viewable area must be lower than 4 if you wish to display its data in Table view.

This info is not "verified", but rather what I have "noticed" to be its behavior.

Comment...thanks again!

 

[comment]

1 hour ago, dmontano said:

Tab Order. The field instance you wish to have display priority in Table view must be set lower than the other field instance.

See, now you have taught me something I did not know. I thought it was based purely on position. 

 

1 hour ago, dmontano said:

This info is not "verified", but rather what I have "noticed" to be its behavior.

Alas, that is too often the case with many aspects of Filemaker's behavior. The documentation we get from Claris is very sketchy on such details.

 

Edited September 2, 2023 by comment

[dmontano]

Ha! I thought you left it to me to figure out how you achieved what alluded me.

20 minutes ago, comment said:

Alas, that is too often the case with many aspects of Filemaker's behavior. The documentation we get from Claris is very sketchy on such details.

I just came across a "need" to show labels only in layout mode and hide all other modes. I have a stack of "panels", for the lack of the right word, and each panel has like 10 fields. Gaining access to any particular panel in layout mode was a pain as I would have to move them one-by-one off to the side to find the one I wanted, work on it, and put all the panels back in position. I needed a way to grab a panel in layout mode, but they were all perfectly "stacked". 

So, in layout, to be able to access one of 10 panels stacked, I created a text label for each of the 10 panels that I could select the label "handle" and bring to front to work in layout mode. It took me forever to find a way to show in layout mode and hide in all other modes my "label" handles.  Get(windowmode) = 4 does the trick. However, no clear documentation about this. There is some info about WindowMode 4, but not in a manner that makes it clear I can use it in layout mode to achieve what I wanted.

See screenshot attached.

Once again, thank you for your time: I really do appreciate it!

[comment]

20 hours ago, dmontano said:

Ha! I thought you left it to me to figure out how you achieved what alluded me.

Actually, I told you how in my very first post in this thread. But it turned out I was wrong, and I got there by accident.

 

Edited September 3, 2023 by comment