Dynamic, Relational Value List

[CalvinFold]

I just *know* I'm missing something obvious, but I've never done this style of value list before. Apologies in advance if I don't post my file, but it has my company name splashed all over it and it's internal...

I have two tables. One of them is designed to hold onto values to be used as a value list in the other table. The idea is if an edit is made to a value, that value changes everywhere it was used in the other table (i.e. the field value changes but the key value remains the same):

TABLE: networkPortLocationList

__kp_networkPortLocationID (primary key)

netPortLocValue (list value)

TABLE: networkAccessTable

__kp_networkAccessID (primary key)

_kf_portLocID (foreign key from networkPortLocationList)

All primary keys are "surrogate" keys.

What I can't seem to wrap my brain around is how to set up the fields in AccessTable so that each record is related to a record in PortLocation, and how the user can access the relationship easily (i.e. via popup). Many AccessTable entries can share a given PortLocation entry.

Also, any time someone changes the PortLocValue the change should cascade to any entries using that value (base on the PortLocation key).

Near as I can tell this means I can't use "Value List from Field" and other such FileMaker constructions (at least when I tried they didn't work), so I'm assuming I need a relationship set-up the right way. I'm a bit new to the use of surrugate key fields, so I keep getting tripped-up. I need to do this sort of relationship repeatedly on this project, so I need to get it right before I dupe-out the idea to other tables.

[bcooney]

On the Access Table data entry, you will format the _kf_portLocID field to be a popup menu using a value list "Port Locations."

The vl, Port Locations is created from the field __kp_networkPortLocationID and the field netPortLocValue. You could choose to only show the second field (netPortLocValue) and sort by it.

This is not a related value list. You're not limiting the choices available in the value list at all.

Since you are storing the _kf_portLocID field, if the netPortLocValue changes in the networkPortLocationList table, the change is seen on all records that have the respective _kF_portLocID.

[CalvinFold]

Sorry it took so long to respond, I wear alot of hats around here...

Can you give me some places to look if I did the above (which is what I had also suspected I'd have to do) and it's still static and not dynamic?

If I have something like...

ID = 1

Value = 2nd Floor

...in the PortLocation table when I make the selection in Access Table, then change the Value in PortLoc to...

ID = 1

Value = Second Floor

...the popup in Access Table still says "2nd Floor" (the originally chosen value).

Is this some sort of storage issue? Or is something missing from my definition of the Value List? Just grasping at straws really...

As always, thanks in advance for your help.

[EDIT: Ooops, nevermind...got it working now. Was staring at an old record. The old ones stared improperly at the Value, not the Key, so they aren't dynamic despite the changes I made. Gotta reset my test data...new entries are playing nice the way they should. THANK YOU!]

Edited February 20, 2007 by Guest

[CalvinFold]

Followup question:

Given the above relationship and popup list...

Instead of physically deleting obsolte or invalid entries in PortLoc, I have a ValidState field that says "Valid" or "Obsolete."

Is there any way to build logic into the relationship so that the popup in AccessTable only shows the values that are marked as "Valid" and does not show the values that are marked "Obsolete"?

Even more advanced I suspect, is that ideally any values in AccessTable whose values have been marked Obsolete in PortLoc should also be flagged somehow in AccessTable as needing attention (i.e. the user needs to re-assign to a "Valid" value).

[Genx]

Add one more item to the relationship:

Add a calculation field result number: "1" being the calculation

Relate this to your Valid field (assuming you use 1 to specify a valid entry, if you actually use the word "valid", then change the calc field mentioned above to be result text and the calc "Valid" )

[CalvinFold]

I can't seem to figure out how to add a calculation to a relationship. I get the dialog with the tables and their fields on the left and right, and an operator popup in the middle, but no way to enter anything else.

I assume I'm missing some critical detail again.

Apologies in advance for the n00bishness...first time I've ever had to do such complex relationships.

[Genx]

Not the most basic example but it's all that came into my head...

Let me know if you are having trouble understanding anything.

conditValue.zip

[CalvinFold]

Wow, is that really the only way to do it?

As I understand it, in order to "filter" the relationship I will need to have a field for every "filter value" I wish to use. That sound about the gist of it?

That just seems "messy" somehow, especially since I'll have to repeat this technique alot in this database.

But if that's my only option... *shrug*

Would there be any problem setting the field to get it's value from a custom function? My thought was to put the filter value (in my case, "Valid") in a custom function like a database-wide constant. That way, if for any reason it has to change, I can update all the relationships by changing the constant once. Would that work the way I think it should?

THANKS!

[CalvinFold]

I set-up the relationships in my database per your example (using the constant) but the relationship does not seem to affect how the popup displays values. It's still displaying values with a ValidState of "Obsolete."

I noticed your example is a three-table system, whereas mine is a 2-table system:

netPortLocList

This table stores the possible popup list values for the other table:

• __kp_netPortLocID (key field)
  
• zrec_ValidState (whether a value is "Valid" or "Obsolete"...there are no value deletions for history tracking reasons)
  
• netPortLocValue (the list value)
  
• Some other dev fields like timestamps and such.
  

netAccesTable

This table uses netPortLocList's values to generate a popup of available port locations for a given access point. Only list items with a ValidState of "Valid" should show-up in the popup.

• __kp_netAccessID (key field)
  
• _kf_portLocID (foreign key from __kp_netPortLocID)
  
• zdev_Filter_Valid (calculated field using a custom function storing the value "Valid")
  
• Some other dev fields like timestamps and such.
  

Is there an ingredient I'm missing?

Thanks in advance for your patience...

[Genx]

Can you attach an example of your file please as you've got it set up right now.

And re: lot's of fields all over the place, that's why we preferably use a 1 or 0 to specify dead or alive.

[CalvinFold]

Unfortunately I'm not at liberty to post the database due to company policies. If you need details, just ask, I may be able to provide screenies or descriptions.

Sorry.

Can you give me an example using only TWO tables so maybe I can figure out how to adapt it from that example?

On a side note, I appreciate the "1" and "0" thing. But there is an off chance I may need intermediary levels besides "on" and "off" in some areas and I wanted to be consistant with terminology. I find real-English easier to use when I have long gaps between working on the project (something always seems to interrupt). Well, that and over-documenting everything I do.

I do appreciate your patience with me.

Edited February 21, 2007 by Guest
Added more

[CalvinFold]

I've attached a screenie of the Relationships screen.

For your purposes, ignore the extra table instances with the "_Self" suffix. Those are self-joins needed for a user interface trick I'm using and don't affect anything else.

Edited February 21, 2007 by Guest

[Genx]

Attachment? And I was suggesting you recreate that particular portion not post your actual file :(

[Genx]

Which is your main table?

[CalvinFold]

Define "main." Technically, both these tables are sub-tables in a much larger framework. I'm building bottom-up.

In this pairing, it works left-to-right, with [color:brown]networkAccessTable is the "main" table, with [color:brown]networkPortLocationList only serving as a means to dynamically manage a popup list (i.e. it stores the values).

The two tables suffixed with "_Self" are instances of the table with the similar name they are joined to and are simply for user interface purposes.

[color:brown]zdev_utility is just a place to stick global utility fields, mostly for user interface purposes. This particular system will be disabling much of FileMaker's built-in interface in favor of it's own.

[color:brown]zdev_activityLog is a log of all operations for historical record. Since much of the UI is controlled via scripting and custom elements, this log can easily track every change made in any table from any script-driven element.

[Genx]

This one should be more straight forward, choose a state and then an active suburb from the list.

address.zip

[comment]

Maybe I am missing something, but it seems it can be much more straightforward than that.

Values.fp7.zip

[Genx]

I'm just confused comment. What's that got to do with value lists conditional upon all items being valid?... Maybe i'm missing something

Edited February 21, 2007 by Guest

[comment]

Sorry, missed that part (another sign of this thread dragging on...). This way, then?

Values.fp7.zip

[CalvinFold]

There must be a piece of the puzzle I'm still missing, some setting or concept my eyes and brain are skipping over.

(My apologies if this is "dragging"...but this isn't covered in the five different reference books I have here at my desk. I honestly do try to beat my head against these things first before coming to forums.)

Three screen shots attached. One is the Relationship dialog as I've got it set. The other two are the two different ways I've tried the Value List. One is all values, which will cause the popup to display all values, whether Valid or not. The second shot was just my blind attempt to mimick the "include related values from" I saw in your example, without truly understanding exactly what you're doing with it. When I do this the popup says no values were returned.

VLandRel.zip

[CalvinFold]

Can you explain what's going on in yours? It seems to do what I'm after but I'm trying to figure out what [color:brown]cActiveValueID is doing exactly and how it all fits together so I can put it into my database properly.

It does seem simpler, but I want to understand it in case there is a catch for my particular setup.

THANKS!

Edited February 21, 2007 by Guest

[Genx]

I think comment was complaining about me causing it to drag on not you.

Anyway, Try taking out the ID field out of the relationship ( _kf_portID = whatever) and just leave the calc field linking to whether it's valid or not -- PortLocations2.jpg is set up correctly assuming you want a list of all valid ports (if you change the relationship as described above)

[CalvinFold]

HUZZAH!

That worked! The popup in my layout to create a new [color:brown]networkAccessTable record is giving the expected values.

Of course this solution caused another layout to start showing the key field instead of the value, but that I should be able to suss out.

Also need to really sit down are stare at what you just showed me to be sure I understand the trick, rather than just knowing it by rote.

I'm still curious about [color:purple]comment's method, which is also interesting.

Much, much thanks!

[comment]

cActiveValueID is returning the ValueID of the record - IF the record is flagged as active. Otherwise it returns nothing. Since the list is based on values from this field, only active records show on the list.

You could achieve the same effect with a relationship, but it's more complex, IMHO (see attached). Same amount of fields, but one TO more.

Values2.fp7.zip

[CalvinFold]

To both [color:purple]comment and [color:purple]Genx...

From a strict point of view, from that "best practices" kind of view, which is better: doing this via calculation or via a relationship?

Mind you, calculation may be simpler, but that's not what I'm asking.

For example, using Surrogate keys is often way more annoying than Natural keys, but in the end are generally "better" from a design standpoint, and often required in corporate environments. In my case it's "best practices" for database design for my company. (Okay maybe not the best example from a professional point of view, but best I had at-hand.)

So I'm looking to do this the "right" or "accepted" way, rather than the "tricky shortcut" way. Trying to future-proof my own work. :

[Genx]

It entirely depends on what you're trying to do...

In this case it's probably easier and a better idea to use comments method, but when you start having to filter these lists by more complicated criteria than just a particular value being present, it will probably be easier to use the relational method (my opinion only, others might differ)

[CalvinFold]

Actually the kind of feedback I was looking for. Knowing how my department works, anything I can do to "futureproof" or "modularize" or make more flexible, just in case, usually is worth the trouble.

We tend to "shock develop" way too much (get it done *now*, messy or otherwise) and then pay for it later when modifications require re-writes of large chunks of things. When I get the rare chance to take my time, do things right, and plan ahead, I try to take advantage of it.

Spent weeks on this one just getting a proper ERD together in Visio and reviewing with a good friend and database guru, before ever sitting down with FileMaker. Not often I get to actually *plan* a databse/programming project.

THANKS!

[Genx]

Glad we could help you out :