Layout for searching sub-tables of sub-tables

[benwiggy]

I'm trying to create a layout, which is used solely for searches.

Let's say it's a database of Cheese-producing Nations. (It isn't.) Each main record is of a Nation and its cheese production data. Each Nation has a sub-table of farms. Then the farms have one-to-many attributes of cheese types, Delivery options, and err.... some other thing. Each of these is in its own table with an ID and a Value List.

I have created a layout with a field for each of the three types, as a drop down list, and a portal that lists the farms and their Nation. I can select a value from the drop-down list, and perform the search, and I get a lovely list of results.

Here's the problem:

When I run the search, the name of the cheese in the cheese field changes. It seems to revert to the first-entered attribute for the first Farm in the portal list.

So if I want to print the results of my search, I can't tell what cheese I actually searched for.

The layout is based on the Farms table. Is that the problem?

Do I need to have a button and script to execute the search in some modified way?

Or do I need to modify the drop-down list -- perhaps a different field from the actual cheese field -- in order hold the value somehow?

Hope it makes sense.

Don't know if this is a layout question, a searching question, or a database design question.

[bcooney]

I'd FIND from the last related table. Use value lists on the find layout. Yes, the user can choose mismatched values to find, but they'll not find records.

[benwiggy]

I'd FIND from the last related table. Use value lists on the find layout. Yes, the user can choose mismatched values to find, but they'll not find records.

I think that's what I've done. I've created a layout with fields from the last-related tables, as drop-down lists from the Value Lists. But my problem is that the value CHANGES when the search is performed.

[bcooney]

Are you in FIND mode?

[benwiggy]

Yes. I've set field entry to be possible only in Find mode in this layout.

[bcooney]

Well, it's not possible for a selection from a drop down while in Find mode to edit existing data. Can you zip and post a sample file?

[benwiggy]

I'm extending an existing database with these sub-tables, which is already quite large and full of data that I can't really stick up on the internet.

I'll see if I can create a dummy, but it might take a bit of time.

What I meant was that I've unchecked the ability to add text in Browse Mode.

[bcooney]

Continuing to talk in the abstract won't get us far. I'll look for your dummy file. BTW, I always design a dedicated FIND layout rather than use a data entry/edit layout for finding.

[benwiggy]

Yes, it's a dedicated layout for finding stuff.

But as I say, my problem is that the results change the data in the search field, I suspect because the search fields aren't in the table on which the layout is based.

This may be the cause of my problem. But as the three fields are in separate tables, I'm not sure how else to go about it.

[bcooney]

If you place fields on a layout dedicated for Finding, the fields must be related from the context upon which the layout is based.

For example, in an Invoice<LineItems solution, if I were to base a layout on the context of the LineItems table, and wish to Find for Invoice Date, I can't just plop an Invoice Date field on the LineItems Find layout. I need the relationship up to the Invoice from Line Items. Check your relationship graph. You are sitting on the table occurrence upon which you've based the layout. Any fields in this table occurrence are OK and any related fields in related TOs are OK.

[benwiggy]

I think I understand what you're saying, but not convinced that it's relevant. As I've said: the search works correctly. My only problem is the DISPLAY.

If the Cheeses for a particular farm are: "Cheddar", "Stilton", "Wensleydale", and I search for "Wensleydale": then I get the farm record, but the Cheese field now displays "Cheddar", because it's the first record in the sub-table. The drop-down list in the Cheese field isn't a portal, so doesn't display all the Cheese.

Obviously, if I get several farms as results, then I don't want to display all the cheese that all the farms make. I just want the value that I searched for to be displayed.

Here's the dummy database. If you use the Find Layout to find "International" Delivery options, then you get "National" appearing in the field when the search completes.

Cheese.fp7.zip

[David Jondreau]

You don't have a dedicated layout for finding stuff. You have one layout that you're using to find and display results. Barbara's recommending two layouts, one for the find, then you switch to the other to Browse your results. Personally, I prefer using a hidden tab control and an OnModeEntry script trigger to flip between find and browse, YMMV.

You're searching for farms. It's not clear why you want only the related item you searched for to show up on your results (rather than all the cheeses, or no cheeses).

To get the result you want, I think you're going to need to script the find. That script will check to see the choices users have made in Find mode and display them in a merge variable or global field on your Browse layout/tab. That script can run on ObjectModify on the field or on OnModeExit. There's several ways to accomplish this.

[benwiggy]

Ah, I hadn't appreciated the distinction that "a dedicated find layout" meant I would need a layout for entering the find, and another for displaying the results. Thanks.

The purpose of displaying the search criteria is so that when you look at the list of Farms, you can see what it represents. "Here's a list of farms that make Cheddar."

I thought that a script or something might be necessary, but that's a great pointer. Thanks.

One of the things I seem to be learning about FM is that I need twice as many layouts and fields as I actually planned...!

Once again, many thanks.

[bcooney]

Thanks, DJ, for stepping in. I am swamped and didn't have time today.

[benwiggy]

Personally, I prefer using a hidden tab control and an OnModeEntry script trigger to flip between find and browse, YMMV.

That script will check to see the choices users have made in Find mode and display them in a merge variable or global field on your Browse layout/tab.

I can't find any script action that takes me to the 2nd tab.

Also, how do I "get" the Find parameters and stick it in text box. Do I need to create another text field, simply to pop the search criteria in?

Thanks

Nor indeed one

[David Jondreau]

Upon further reflection, I think you need a few smaller things:

1) OnObjectExit script trigger. Attach this to your Find boxes and ran a script that sets a global variable to the values in each of your find boxes.

2) Merge Variable: Add the global variable you're setting to your layout.

3) Conditional formatting: On the Find boxes, set the conditional formatting so that in Browse mode, the font size is 500.

4) Field entry: Prohibit field entry on those Find field in Browse mode.

[benwiggy]

THanks. Will crack on with some experimenting and let you know how I get on.

[benwiggy]

Sorry, I'm making heavy weather of this.

1. First, how do I force Find View when I go to the layout? I've tried using a script, but I can't find a command that does this. And there's nothing in the Layout Setup about Find mode.

2. Again, I can't find a suitable script command that will let me enter a calculation to get the value of the field.

3. I still can't find a Script command that switches to the second Tab.

Or is there a better way of doing the whole thing?

I can't believe that this problem is not commonplace. I want to find those records that contain a particular sub-record. But in the field where I entered the search criterion, I don't want to see a sub-record that is unrelated to my search in the results. (And I do want to see the criterion I used, so I can print off the results and know what they are!)

Thanks again.

[bcooney]

Ben,

Take a look at this demo file I created a while ago. It contains the Find script that I use in all my solutions. Perhaps this'll help you understand how to use Find.

Barbara

FindDemo.fp7.zip

[benwiggy]

A-ha! Of course, it's been staring me in the face the whole time: "Enter Find Mode" (obviously.) Didn't see that before. Doh! Thanks.

And, I have also found an answer to No. 3 in this thread:

http://fmforums.com/...ns-and-scripts/

Go to Object [Object Name: "TabName"]

works, where TabName is a name you have added to the Inspector's Name field.

So hopefully, the bit about getting hold of the find criteria should follow.....

[benwiggy]

Sorry, still not joy with the script to get the search criteria into a global variable. PErhaps I should ask in the scripting section?

Also, my results layout isn't really working. The portal is giving me either all the Farms in that Nation, or it gives the results in separate records that need flicking through, rather than in one portal.

Is there a better way of getting a Layout to provide a list of records that match some search criteria?