Displaying records from one table

[GeraldV]

This might not be the correct section for this post, but since I am now working on my layouts...

I have a single table of Companies. Fields include Name, Address, Phone, etc., and a Category field. The Categories are set from a custom value list (Manufactures, Suppliers, Agencies, etc.)

In a basic layout I can view all records and page thru them. I would like to set up tabs, based on the Categories, and view the same info, but restricted to the particular Category. Is there a way to do this with a single table?

[bcooney]

Calling David Graham! Sorry, but he has a technique for putting all parties in a table with "satellites" to their type. It's a bit advanced, but maybe worth looking at.

Anyway, if not using Graham's Method, you can create filtered portals to Companies on a layout that is based on another table (possibly a Sys table).

A Relevant Thread

Edited December 16, 2008 by Guest

[GeraldV]

I had already looked at the Graham Method examples, but I am kinda (ok, extremely) confused. The only way I could see what I want to occur is to create a different table for each "category", but then the user would have to know which category the company was to go into before creating a new record, and thus start out on the correct layout (ie-Manufacturers for a Manufacturer), rather than just entering the company data and figuring out what category to place it in later. I must be completely missing the concept!

Is there an extremely basic example available?

I've been trying to recreate this in it's most simpliest form and have had completely no luck. I can view things through portals just fine, but that really isn't what I am wanting to achieve.

[comment]

I must be completely missing the concept!

No, you're not. What you said is exactly right. The "supertype" method* is suitable for maintaining a common table for many DISTINCT types, for example when Income and Expenses both require a corresponding entry in a common Ledger table.

Perhaps what you want could be achieved simply by having the buttons perform a find for the relevant category?

---

With all due respect to Mr. David Graham, I have used this method as far back as version 4, so I don't feel bound to change the accepted nomenclature in his honor.

[GeraldV]

Ok, but when examining your CommonTypes example, I see no way to be able to move records between "type" tables. My concern is: if the user creates the entry under the wrong "type" then there is no easy way to move that record to the correct "type" table.

[comment]

Perhaps I wasn't as clear as I'd like to be. Let me try again: No, you haven't missed a thing - the supertype model is NOT suitable for the situation you describe (IMHO). If all that's separating one type of company from another is the category, you'd best keep them in the same one table.

[GeraldV]

Then that would bring me back to my original post, unless there is a way to do the supertype solution in reverse, starting from the common table, and populate the type tables based on the user's selection. (I'd prefer to use the supertype solution, but I KNOW my user base)

[comment]

There's no problem in implementing the supertype model from either direction. However, this time I'm afraid you may be missing something: the supertype/subtype is a DATA MODEL. You implement it to fit your real data structure - not to provide some user-interface feature. Most often, it is needed when the subtypes require different attributes (fields), in addition to the common ones. In such situation there can be no question of 'moving' a record to a different subtype. It cannot be moved, because it wouldn't fit in there. But when you have two entities with ALL attributes in common, and the only difference between them is the CONTENT of these attributes, then all you have is one entity and one table.

So the answer to your original post is to create buttons that will reduce the found set to the selected cstegory, and make sure users cannot extend it beyond that - at least not on this particular layout. The butttons can be styled as fake tabs, and no one will be able to tell the difference.

[bcooney]

OK, so let's forget the supertype/subtype data model (although I love it!). It is confusing, even to non-beginners.

On your list view of Companies, you can "hard-code" buttons that find by company type. So the Find Manufacturers button would simply perform a find in companies for type="Manufacturers" or the ID associated with type Manufacturer from your CompanyType table. Or use the tab panel to view different filtered portals.

Can a company have more than one type?

[GeraldV]

Actually, I could set up the database either way. There are a lot of other tables/attributes that are related to the individual types, but I think for the sake of importing, one main table would be easiest ( although, for exporting, the supertype would be much easier. ) Then again, it really wouldn't really matter which I went with if I could rely on the end users not making a goof. I think I really need to weigh the pros and cons of both models ( maybe a good night's sleep would help!!! ).

How hard would it be to create script that could "move" a supertype?

...and, no, there can only be one "type" assigned per company

Edited December 17, 2008 by Guest

[comment]

for exporting, the supertype would be much easier.

How so?

How hard would it be to create script that could "move" a supertype?

Not very hard (though harder than a script that finds records in category and exports them...). But that's not the point. Data shouldn't be routinely shuffled from one table to another. Moving data is vulnerable - what if there's a power failure right in the middle of the process?

You seem very intent to implement a data model that multiplies the complexity of your solution by a factor of 10, and for no good reason that I can think of.

[GeraldV]

Actually, I am trying to keep it as logically simple as possible. I would never want to generate a report/export of just the common data unless it was an overall dump. I WILL have to generate reports/exports based solely on the "type" and it's related tables. It would seem easier to be able to do that based on the supertype model. Plus, it would also limit access to the related tables so attributes from one type would not "accidentally" be assigned to another (this would include Parent/Child relationships). Things were much simpler before I started thinking about trying to make it idiot proof user friendly.

Edited December 17, 2008 by Guest

[comment]

I WILL have to generate reports/exports based solely on the "type" and it's related tables. It would seem easier to be able to do that based on the supertype model.

Again, how so? You can certainly have a script that finds records in Category A and exports them, exporting data from fields a, b and c. Another script would find records in Category B and export fields a, b, d and e.

I'm afraid I wasn't able to follow the rest of your description. I don't think I have anything to add to what I already said anyway, and I am not out to convince you this way or another.

[GeraldV]

I really appreciate all the feedback and please DO try to convince me.

Even though I've been trying to work out the basic functionality, nothing has been set in stone as for the actual model to use. I know I have 7 categories under which a company may be placed. A company may have sub-companies (children). A (parent) company may have related tables based on the category (ie-a Supplier would list its Products, a Manufacturer would list its Brands). There are other certain attributes which will only be available to some categories, but every company does have the basics of name, address, etc...

Any frank opinion on the best method/practice to subscribe to is more than appreciated, as I see benefits and pitfalls in either method I choose.

Of course, I realize, none of this relates to the original post.

I went back and re-read your discussion with Graham http://fmforums.com/forum/showtopic.php?tid/198682/ and see what you mean. I also went back and revisited the old FoxPro database I am trying to replicate, and it appears to use both models, so I am further confused. I think I will just approach it from where I originally started as an extremely simple model, and try to build in the extra "goof" protectors.

So that would bring me back to my original topic, with the question: How do I stay on the same record when switching views with a filtering script? It works going back to the unfiltered view from the filtered view, but not vice-versa.

Edited December 17, 2008 by Guest
After thoughts

[comment]

I really don't have anything new to say. I based my answer on your original request to page through records in a selected category only. There may be other factors to support the superype model. Or perhaps you should put the companies in separate tables that have nothing in common. There are benefits and pitfalls to each - but I believe you now have enough information to make an informed decision.

How do I stay on the same record when switching views with a filtering script? It works going back to the unfiltered view from the filtered view, but not vice-versa.

What do you mean "on the same record"? If you switch layouts, and the new layout belongs to the same TO, you're still on the same record. I am not sure what you mean by "filtered view". In Filemaker, you "filter" a set a records either by performing a find or by a relationship.

[GeraldV]

I was following the suggestion by you and bcooney with buttons styled as fake tabs. Reducing the found set by type= "(insertnameoftype)". So let's say I have 3 tabs(layouts) Main (unfiltered), Type1 (find type1), Type2 (find type2). If the user is viewing in Main and wants to view a record with all it's related info, they would click on the appropriate "tab" and be taken to that layout. Problem is that because of the find being performed (to limit files of that type), it resets the displayed record to the first record found in the find. I would like it to switch layouts, limit the type, but stay on the same record the user was on in Main (providing that record is of the correct type, of course)

[comment]

If the user is viewing in Main and wants to view a record with all it's related info, they would click on the appropriate "tab" and be taken to that layout.

No, if I am following this correctly, the user should click a button in the body of the record and be taken to another layout of the same table, this time in form view. When they return to the list, they will still be on the same record. You could also open a new window for this, and close it when the user wants to return to the list.

[bcooney]

I believe that you've misinterpreted the two interface ideas. Given that you have one table of companies, and there's a field for type:

1. List view with hard-coded Find buttons by type. "Find Suppliers" would find companies of type "Supplier".

2. A form layout with a tab panel. Each tab panel contains a filtered portal for its respective company type. Sounds fancy, but not as useful in the long run as #1.

[GeraldV]

Ok, I think my brain is preventing me from including all the pertinent information to the question.

I would be switching from a form layout to another form layout, both based on the same table.

So, if I am on record id#21 in the "Main" (unfiltered, all records) form layout, and I click on a fake tab (a button scripted to take me to the "Manufacturers" form layout and find all records of type "Manufacturer"), I would like it to "update" that form layout to put me on record id#21. So really, I would like the script to "remember" what record I am on, take me to the new layout, "filter" by finding all records of type, then put me on the "remembered" record. I am having a hard time trying to get it to put me on the "remembered" record, I can get the script to "copy" the id field, but not do a goto that value. So basically doing a find within the found set, but trying to keep it unconstrained. Gosh, am I making any sense?

[comment]

How would the 'Main' form layout be different from a 'Manufacturer' form layout?

[GeraldV]

Same, but based on type, would also include 1 or 2 list portals of related items and extra attributes. It would basically be just a more in-depth view. I could just skip all that and place the all the various portals and attributes on the Main window, but that's just not very clean looking. Really everything "could" all be done from one form layout, I am just trying to narrow things down for the user.

[bcooney]

Perhaps you need a form layout for each Company type, because you want to show different children in portals.

Then your nav would include a "go to proper company form" section.

[comment]

Same, but based on type, would also include 1 or 2 list portals of related items and extra attributes. It would basically be just a more in-depth view.

All this could be achieved by a single layout with tab panel control. But even if you have separate layouts for each type, you would still stay on the same record when switching to another layout (provided, of course, that the layouts are all showing records from the same table occurrence).