Rookie Layout Question

[Phillip Anthony]

Have created a very basic database to keep track of DIY expenses. I've created separate layouts for electrical, plumbing, etc. Each layout is identical except for the receipt number, vendor (a pop-up menu), and the date. Although the other fields are identical they all have unique names, such as: elect_cost, elect_qty vs plumb_cost, plumb_qty and so on. The database works fine except for the following.

When viewing, for instance electrical, I do see what I entered into electrical, but I also see partially filled records which are actually from the records of other layouts such as plumbing or other layouts. The fields that appear with data the 'vendor', 'date' and 'receipt number' fields. Is there someway to prevent this. I could remove the pop-up menu and just type in the vendor name, but this is more work.

Any help would be appreciated. Thanks.

Phillip Anthony

[-Queue-]

In versions < 7, a layout is simply a way of displaying data contained within a file. Particular data is not connected to a particular layout. So if you have plumbing records found, it matters not if you are on an electrical layout or plumbing layout. The data for all found records will be shown.

What you may need to do is script navigation of layouts (removing them from the Layout Menu, so they can not be easily accessed), finding and displaying only records appropriate for the current layout via a category or type field.

Something like

Enter Find Mode [ ]

If [status(CurrentLayoutName) = "electrical"]

Set Field [Category, "electrical"]

Else

If [status(CurrentLayoutName) = "plumbing"]

Set Field [Category, "plumbing"]

...

End If

End If

Perform Find [ ]

[Phillip Anthony]

Thanks for your reply. It makes good sense to me. About 5 or so years ago I wrote an fairly complex database for a large refinery using Access and I do not ever remember having the difficulty that I am having with FM. As I recall, I had separate layouts for different units and data from one unit never appeared in any other unit other than its own. As I said that was some time ago, but I never had to script any of it. Could be that Access is geared differently than FM.

Any way I tried to implement the script that you demo'ed for me but ran into a brick wall when I got to the Set Field step. It was difficult enough learning that when in the 'Specify Calculation' window you must be in View by 'Type' and not 'Name' to even see all of the Status 'selections'. What a concept! Any way, I have looked hard but cannot locate the 'Category' function to place between the Set Field brackets. The FM documentation is no help in this regard. The FM Help does not help either. Perhaps I'm blind. But I will keep on trying. I have Chris Kubica's FM 6 book on the way so that may help.

Thanks again.

Phillip

[Fenton]

I think there is a basic concept misunderstood here. Queue said it here:

"Particular data is not connected to a particular layout."

It is connected to a particular record (or row).

The labels you see on the layout may or may not be the same as the field names. They are more or less meaningless to the structure; you can make them anything you want, they're just "text on a page."

What this means is that you need either complete separate sets of fields for each "category" of items (electrical, plumbing, etc.); tedious, but it works. Or you need a separate record for each entry, with an additional entry in such a "category" field ("electrical," "plumbing," etc.). "Category" is just a word we're using, in the usual English sense of the word, it's not anything to do FileMaker functions.

There are pros and cons to either method. I personally would tend to go with the "same fields, category tag, separate records" structure, with self-relationships to add things up.

You may be more comfortable with the separate fields for each type of work, which is more tedious, but in a way simpler.

Maybe you had separate sets of fields in Access, so there was no conflict on your layouts. I don't know how Access does "layouts." But the basic data structure underneath is what matters, and that is going to be more or less the same in any database system.

[RSpisketts]

Hi,

Like you I have worked on Access before and they are NOT the same thing. Whilst a layout is a bit like a "View" on the data, by default you will get all the data returned from the table when you go to that layout.

From what you say, it sounds like your layouts are so similar you could achieve what you want with ONE layout, that restricts the data in some way. Perhaps the following:

1. As suggested above, create an indexed, text field in your table called "Category" (plumbing, electrical, etc).

2. Add this field to your (one and only) layout, attaching a value list with all the categories in it.

3. Perform the find illustrated above (and/or below), to restrict the visible data.

Perform Find[Restore]

where: Find Request: <TableName>::Category:["Plumbing"] (say)

The "find" could be initiated in a script that navigates to the (one and only) layout (and hence, restricts the data before you ever see it), or from a button on the layout (eg. "View Plumbing").

In other words, if you want to see particular data, you have to force it in FM - there is no SQL "View" equivalent to "select * from abc where category=plumbing".

I've attached an example using portals which I got from this forum (can't remember who from), but it illustrates a great way of restricting what data is visible, using filters. Portals however, have there own (finding and sorting) problems which you should be made aware of.

You could also use global fields instead of value lists, for your vendors:

1. Add a global field per vendor type

2. Add a global "copy into" field

3. Each time you click the button, the appropriate global field needs to be copied into the "copy into" field. This then becomes your vendor field (still a drop-down, but almost dynamic).

Anyway good luck.

Roger.

ps. If I am speaking B_____KS, please inform me immediately.

90698-FilterDemos.zip