Retrieve/Display (Get?) text from a field drawn from another record in the same table based on the entry in a third field

[ArchFile]

Hi everyone. What I am attempting seems like it would be relatively easy for an advanced user but I can't figure out how to do it on my own. I don't think I have the proper vocabulary to fully describe the steps I want to take, so I will use screenshots to help illustrate what it is I want to do. Thanks in advance to anyone who can help.

This may look crazy, but the chart is common in defining relationships of excavated contexts in archaeology.

"Context" serves as my unique ID for each record in the table; the table is called "FContexts".

When creating a new record, a new Context number is added, but these numbers are added as text entries, not auto generated as serial numbers (if that matters). These Context ID numbers are also populated in a value list called ContextNumbers (via the option "Use values from field: "FContexts::Context").

In the example shown in the screenshots, the context number of the current record is displayed in the field "Context" in pink.

Clicking on fields "C_Earlier_1", "C_Earlier_2", or "C_Earlier_3" will display this value list ContextNumbers as a pop-up menu. Users can chose any of the populated ContextNumbers values as an acceptable response. Here, I have chosen 213, 223, and 228 as entries in these three fields:

Now, because 213, 223, and 228 represent other records in my table, and because they also serve as unique IDs, I now want to be able to pull the text from a text field in each of these records and display it in the associated fields "C_Earlier_1_Desc", "C_Earlier_2_Desc", and "C_Earlier_3_Desc". The field I want to display here is a text field called "Description".

So, any ideas on how to get the Description entries from 213, 223, and 228 to display in the associated text boxes above them? I assume it would be possible by defining a calculated value for the fields "C_Earlier_1_Desc" etc. but I don't know how to build an adequate calculation for this. Would I use some sort of Get function?

Thank you for any help!

Edited August 19, 2015 by ArchFile
pasted figures didn't show up in the body of the post, so I had to attach them.

[comment]

I don't fully understand your description. Are those three independent self-join relationships? If yes, you can simply place the three related Description fields onto the layout (you may want to make them non-enterable to prevent accidental modification). Not sure what you mean by "display in the associated text boxes above them", though.

As an aside, the entire setup seems weird: you seem to be saying that when creating a new record, you must manually link it to the three most recent records in the same ? (table? / category?). That's not a good arrangement to have, if only because it invites data entry errors.

[ArchFile]

Thank you for the response. There are currently no joined relationships, although I am wondering if that is something I need to do to achieve what I want?

Concerning the setup, I can imagine it seems weird, and I'll try to better explain because it may help find a solution. I'll try to spare you a boring archaeology lesson, but some explanation seems warranted to explain the logic here. Also, apologies on not knowing all of the Filemaker terminology, I am still learning.

The three "C_earlier" entries located above the current record's "Context" are not the three most recent records, nor are they based on any calculation. These numbers must be manually entered by the user because they are based on observation. Archaeologist users can enter any of the previous contexts (from 1 until the number of the current record) that the they have excavated and that are entered into the database as a record. Thus, an acceptable response in any of these three fields can be a Context number from the previous record in the set (in the case of my example, that would be Context "255") or the context from the very first record in the set (Context "1"); it is up to the archaeologist to know and enter the correct context number in these "C_earlier" fields. 

To simplify: perhaps forget that there are three C_earlier fields. Pretend there is only one: C_earlier_1. If the user choses "233" from the value list in C_earlier_1, I want to write a calculation for C_earlier_1_Desc to "Get" (I originally said "display" to describe the result I want but perhaps "Get" is the proper function?) the text from a field called "Description" in record 233. How would I write a calculation for "C_earlier_1_Desc" to do this?

If calculations aren't necessary and this is simply a self-join relationship as you suggest, can you give me a few hints as to how to do this? I'm a newbie. (I agree to make the Description fields non-editable, that makes sense).

Really, thank you so much.

[comment]

I'll try to spare you a boring archaeology lesson, but some explanation seems warranted to explain the logic here.

Completely agree.

These numbers must be manually entered by the user because they are based on observation. Archaeologist users can enter any of the previous contexts (from 1 until the number of the current record) that the they have excavated and that are entered into the database as a record.

Ah. So a context could be related to any number of other contexts - not just three. Now we need to establish two more points:

1. Is this a many-to-many relationship? In the terms of your example: can "233" be legitimately selected as the earlier context of more than one context?
2. Is there anything you might wish to record with regard to a specific join of a context to an earlier context? For example, does the order matter (as in your original description, where the three selections are distinctly marked as 1, 2, and 3)?

[ArchFile]

That's right, a context can be related to any number of other contexts.

1. Yes, 233 can be legitimately selected as the earlier context to more than one context.

2. I think the answer to this question is no, or at least I can't think of anything. In terms of your specific example, the order does not matter: I named each field with a 1 2 and 3 only to have a unique name for each of these field in the database.

[comment]

The simplest solution here, IMHO, would be to enter the related context IDs as a return-separated list into a single text field. Then define a self-join relationship of the Contexts table (using a second occurrence of the table on the relationships graph), matching the new text field against the ID field, and use a portal to the second TO to display any fields from the related records.

The user interface to this could be fancied up in many ways  - for example, you could format the field as checkboxes, using a value list based on the ID field (a field formatted as checkboxes holds a return-separated list of the checked values). You could also make the value list a conditional, to display only IDs that are lesser than the current one. But the underlying structure can be as simple as that.

One disadvantage of this simplicity is that producing a report of all contexts showing their previous context becomes more difficult. Strictly speaking, a many-to-many relationship should be resolved by adding a join table, where each pair of joined contexts would be a separate record. But I believe the simpler alternative could serve you quite well, at least for now.

 

[ArchFile]

Thanks so much for your thoughtful response. You make a good point on combining the related contexts into a single field. That does make practical sense. The only downside to this is that the flowchart layout is based on existing conventions in archaeological data recording (albeit I am taking a recoding method traditionally written on a paper form and trying to digitize it in filemaker).

The related contexts, as shown in my mini flowchart, will be used as a guide to create a master flow chart, or "matrix" of all excavated contexts, which ends up looking like a giant tree (this is created in a separate flowchart program). Students are also users of this digital form, and therefore, I think it is important to maintain that each context is represented in the tree by a box with a single context number inside it.

This mini flowchart is thus a way to visualize, at a glance, the relationship of the current context to the surrounding/previously excavated contexts. Because visualization is key, I wanted to have the brief description of these related contexts displayed next to each context number, so that the user doesn't have to navigate to these records in the database to remember what they were.

However, your suggestion sounds very promising. I will give it a go; I've never completed the steps you suggest (creating a second occurrence of a table and using a portal, etc), but it sounds reasonable and logical. Thank you for your help and patience!

Edited August 19, 2015 by ArchFile

[ArchFile]

Just to clarify before I get started: Can I still generally follow the steps you laid out but maintain the individual fields (one context to each field) or will this method only work if I enter the contexts into a single field as a return-separated list as you suggested? Thanks a bunch.

[comment]

The only downside to this is that the flowchart layout is based on existing conventions in archaeological data recording

You can easily produce the flowchart shown in your original post, by placing three (or more) one-row portals on the layout, and setting their initial row to 1, 2, 3, etc. In those portals, you can place both the ID and the Description fields from the related TO, to accomplish your goal of displaying them together in the same "box".

However, you will never see more "boxes" than the number of portals you place on the layout. Although the data structure I proposed allows a context to be related to any number of other contexts, the display is not as flexible - at least not in the horizontal direction. Vertically, you can have a portal with as many rows as the height of the screen will allow, and you can scroll it to reveal even more and more related records.

 

Can I still generally follow the steps you laid out but maintain the individual fields (one context to each field)

You could, but then you would have to either construct three individual relationships, or add a calculation field to concatenate the three fields into a return-separated list to be used as the matchfield.

Much more importantly, this would limit you to a maximum of three related contexts, and do so at the data level. This would go against what you said earlier (if I understood it correctly). Display issues can always be solved by doing more work (for example, you could use different layouts to show different records, based on the number of related records, or employ a web viewer to build the display dynamically). But if your data model cannot accommodate all the information you need to store, then that's the end of it: the additional information will simply not be there.

[ArchFile]

All very helpful guidance. I will report back once I have time to play with all this and learn the steps. Cheers!

[ArchFile]

Ha ha! It worked! That is, your instructions from earlier were very clear, but since I am inexperienced (what is a self-join relationship? How do portals work again?...etc...) it took me time to figure out exactly what to do. Now it makes perfect sense why you said to enter all related contexts as a return-separated list into a single text field. THANK YOU for sticking this out with me, the archaeology gods are looking down upon you favorably.