Relational Value lists, Find Mode

[NRT.Rob]

Hey all,

sorry if this is a dupe question.. I did try to search for it first, figuring I can't be the first to run into it.

assume I have only two databases: Parts, and Contacts. Parts have a field called "part types", where I can indicate if a particular part is a tool, pipe, or a fitting. (in the real world, there are many other options).

In contacts, I want to show what tools a contact owns, what pipe, and what fittings.

I would LIKE to use relational value lists for this, where in contacts, there is a checkbox set for each category (trust me, it is workable from a data entry standpoint).

I set this up, and found that the relational value list shows "no values defined" in find mode. Pondering this, it also explains some of my dissapearing pop up menus elsewhere in my databases during find mode, though that was never a real problem so I never thought about it much... now it's a problem, because these fields need to be easily searchable, using the check boxes if possible. Again, it's a data entry issue.. without check boxes, the find gets real complicated for the user, real fast.

Is there ANY WAY to make this work in find mode? I've played with indexing and such and it doesn't seem to work. I have found that using all values from a field works, just not "only related values", presumably because the join field doesn't show a value in find mode. But, Having to create separate tables for all of this means literally about a dozen new tables, and I have to find a way to script the populations and maintain them... big, big PITA.

I'm doing all this to move away from custom value lists that add members regularly. again, that's a necessity of my current developement process. Currently these lists are all custom values, and users occasionally add values by hand, which would fail as I am currently developing remotely and syncing the databases on occasion with imports. I'm trying to get to a data-managed value list that will keep itself up to date with imports.

thanks!

Edited July 16, 2007 by Guest

[Søren Dyhr]

Beyond I seems to miss a jointable in you scenario, would I like to know why findmode is manditory?

though that was never a real problem so I never thought about it much... now it's a problem

The rest of us tend to show such matters in a portal of jointable records ...which could give you some indication of the crowd you're gathering here.

--sd

[NRT.Rob]

well, a portal would be useful for showing, say, "all tools" or "all fittings". But if I want to show only the tools that the client owns, to use a portal I'd have to use a join table for the many to many relationship. I think that's what you're saying?

Problem is, my example is very simplified: in my actual databases, That would mean adding literally about a dozen new tables here for all the relationships to be used, then figuring out how to properly populate them and edit them. big, big PITA just to make a few fields use checkboxes in find mode!

I can make the value lists populate with the related information I want to show, I just can't make it stick around in find mode. And this is stuff we need to be able to search by. It's not strictly "mission critical" information, so I'm not worried about strictly correct DB structures for it, but it is a very handy way to quickly find things we need so we can work faster. Thus, my desire for a more quick and dirty way of keeping these both in checkboxes, and populated in find mode.

Thanks for responding Soren!

Edited July 16, 2007 by Guest

[Søren Dyhr]

You can filter what's shown in from a jointables records:

http://www.fmforums.com/forum/attachment.php?attid/11305/

Might it be so that your structure as it is wears unnessersary complexity? there's a great mindopening lesson to learn in this thread:

http://www.fmforums.com/forum/showtopic.php?tid/176396/post/204083/hl//fromsearch/1/

--sd

[NRT.Rob]

Hi Soren,

Yes, I know a few ways I can handle this with join tables; I'm trying to avoid that if there is just some trick to make a relational value list stick around find mode, which would be so very, very much simpler here, and the problem doesn't warrant a major expansion to my database system.

I have my fair share of "ready record" solutions in this database structure already, for "mission critical" problems similar to this where I need to be rigorous. Here, I just want to help my users track and find some incidental information to help them work quicker... without draining the next month of my available programming time :

I appreciate your helpfulness; should I take it then that there is no way to make a relational value list stay populated in find mode?

[Søren Dyhr]

I appreciate your helpfulness; should I take it then that there is no way to make a relational value list stay populated in find mode?

No there is a way, but it's an ugly layoutworkaround require scripting landing the cursor somewhere in a huge pile of the same list, each having their own valuelist attached.

without draining the next month of my available programming time

But that's even more likely if you insist on an inadequate relational structure ...you are apparently not playing right of the sheet of your papermodel. Do not expect arbitrary made links between TO's can make it up for a join table, when it isn't.

--sd

[NRT.Rob]

I'm not expecting anything Soren, I was just desparately hoping that the simple method I saw was workable in some way, even if it wasn't perfect. Since it's not simple, it's not workable. That's really unfortunate.. but it is what is. I guess I'll have to bite the bullet and add all those join tables and work up a system for populating and maintaining them.

Or, I'll set up permissions so my users can't modify custom value lists >: That would probably be easiest, actually...

thanks again for your responses!

[Fenton]

I don't know exactly what your situation is, but I can remember times when I had to use a global field to produce a filtered value list in Find mode. Another fact is that even though you cannot manually enter data into a global in Find mode, you can use Set Field [ ] on one in Find mode.

This lets you do hierarchical filters. But you have to run a script to populate the secondary global filters, using a button over the value list field. Which means a dedicated Find layout, as you cannot allow tabbing into the field; it wouldn't be a good setup for data entry anyway.

The global can be in whatever table, and the value list can start from whatever table, so it's fairly flexible.

Something like that. It's been a while since I had to use this.

[NRT.Rob]

hmmm. I'll check that out. if it's as simple as adding a set field step to an enter find mode script, that would do the trick quite nicely. you can't tab into check boxes anyway (effectively), and I can just replace the enter find mode menu option with an enter find mode script for that database to do it every time find is used. No reason not to...

work or not, it's worth a shot, thanks fenton!

[Fenton]

Oh yeah, I see you want to also change the checkbox field (which would be a global field in Find mode). That would be tricky. How about a script that manipulates the values in a field, using separate buttons over each check box, with a script parameter set to that box's value? You'd have to know what the value was, so hopefully it's a Custom value list. I imagine you could do with dynamic values also, but looking at the position to get the value.

Of course, one begins to wonder why you don't just do the whole thing with global fields in Browse mode. Probably lots of fields?

Edited July 17, 2007 by Guest

[NRT.Rob]

Actually, I just played with a global for a join field. it works like this:

data entry/search field is just text, "customer tools", in a customer file. It has a relational value list/checkbox set reading ONLY Parts, in another database, that are marked as "Tool".

The customer file has a join field, now a global, called joinTool that links all customer files with the tools.

The global STAYS POPULATED IN FIND MODE, and so the checkbox set stays in the customer tool field!

I just added a setGlobal script to the customer database, to set that global whenever the database is opened. The relationship only has to be good when someone is looking at the field to either mark what tool is owned, or searching for people who own certain tools.

as I noted before I have about a dozen other "incidental" relationships that are not critical but are just handy, occasional things to have that don't warrant big database expansions, and this fixes the issue perfectly.

Thanks! I wouldn't have thought that globals stayed populated in find mode, nor that a relationship would calculate in find mode. This is very useful.