two table views on one screen with related child information

[stevenls]

Hi all,

I come from an MS access background and now am using FM 8 in my current employment and can't pull something together like I have done previously. I need to track licenses for products and the users who have the license. So I need in the top part of a screen product information with serial numbers, purchase order info, number of seats on the license etc etc (in a table view).

In the lower section of the form I want to have another table view but to contain all the user information relating to the locations of the licenses. So if I click on a particular Photoshop line in the top, then all the users who have that license allocated to them (for volume licenses) will display on the lower part of the form. I have the table structure in place and have set up the relationship etc etc but I am just scratching my head how to a) set up the two forms I want to have on screen (i.e a form with a user detail sub-form on it) and : making sure the forms (layout) updates properly when I make some changes to the data.

Thanks, I would be thankful for anything that comes from this as I need to get out of the access mindset and it the FM one.

Thanks.

S.

[bcooney]

I can't speak for the "Filemaker Way," but my first instinct is to have a list view (not table, can't do much as a developer with table view) of Licenses (layout based on your License Table Occurrence). Then create a form view layout for each License (also based on the License TO). Go back and put a button on the row in list view that will take you to the form view (it's typically something like "view").

On the form view, have a portal to the users that have that license.

[stevenls]

I am just reading about portals now. So with the layouts I can have a list view at the top and a form or list view in the portal at the bottom also. Reason why a list view for the user info may be better is for volume licenses there may be 20 or 40 users listed and then I may just want to tack one more on or remove someone.

Is there a repository or samples somewhere other than what is in FM as I am raiding them at the moment and they all sort of miss what I am trying to suss out.

Thanks.

[stevenls]

ok. I have a layout now that has the product information at the top of the screen and a portal at the bottom that shows all the users for that product.. schweeeet :

What I want to see at the top half though is a list view of all the products that I can use a scroll bar to scrub through. We may have 10 different purchases of Photoshop so I want to be able to browse for Photoshop and see 10 lines at the top, click on one and see the users in the lower section.. that is where the issue lays now.

[Fenton]

There is more than one way to set up this interface. As you say, one of the considerations how many records you're likely to end up with, and what you want to do with them afterwards.

It could be done with 2 portals. The top one would need to be filtered, by either category, or by alphabetic letters. or both. So you could easily get the 10 copies of Photoshop.

Then clicking on one could filter a lower portal, to show the users for that particular one.

Or, as you suggested, clicking on it could show the users in a list view below, using the Go To Related Record step (see below for more on that relationship). That scenario requires that this be setup in the Users table, View As List, with users in the Body part. The products portal would be in the Header part. You'd likely want, in either case, to highlight the clicked on row; or set the value into an obviously visible field.

The 2 portal interface could be set up in any table, targeting Products and Users (actually it would be targeting a "join" table of UserProducts (or ProductUsers, whichever). The relationship between Products & Users is definitely many-to-many.

The filter(s) for the Products portal would be global field(s), which would be the originating side of the relationship.

This may all sound complex, especially to someone new to FileMaker. But it's not really that bad. Which method you use depends on (as I alluded to):

1. How many user records, and what you're going to do to them. A List View in the User table is definitely the most flexible for working on those records.

2. Where you are when you start this process. If you want a "see everything from one place" master control layout, then the 2 portals is the only option. But it would be crowded.

[stevenls]

ye gads!! Thanks as much as I am confused at least I know this is possible. I tried setting up a portal in the header (where the product info is) but I could only select the user information as the fields for the portal.

I have attached the file in its most basic form for you (if possible) just to have a look at and point out where my set up is off kilter.

Many thanks.

test.zip

[Fenton]

Before we go charging off trying to create a filtered portal, I think we've got to get the basic relational design straight. You absolutely have to manage this through a join table, with an ID from each entity; it's a many-to-many relationship. A join table record is created when a user is assigned software, or when software is assigned to a user. From either side, it is the SAME join record. The data to do with the assignment, dates, etc., go in the join table.

Either main entity, Product or User, can see and add their records in this join in a portal, as well as the matched fields of the other entity (which should be non-enterable, to reduce confusion). To see all users for a piece of software, Find the product, see the users. To see software for a user, Find User, see the software.

Yes, maybe you want a fancier choice portal at the top of a User List layout (which you don't have yet, by the way). But that is not doable until you see how the basic structure works. It is perhaps not needed.

test_fej.fp7.zip

[stevenls]

I can see your point with that so thanks for the file.

I need to have a portal or list view at the top so I can see a larger number of the products. Basically I want to have a portal of say 10 lines at the bottom then a list or portal of the products of the top that I can see about 20 lines of and then I can just scroll through what I need or find a particular piece of software (i.e 9 different purchases of Filemaker for example).

That is my main goal for this.. a product list at the top where I can click any particular line and see all the users who have that at the bottom.

I chucked a portal in from the products table but it only showed one product at a time still, need to get a list view working somehow.

Cheers, appreciate the time with this.

S.

[stevenls]

Hey,

Just wanted to revisit this as I can't find a way to make it work how I want to get it happening. Can anyone suggest how to achieve what I need to achieve as I have outlined above.

Thanks I would love to get this locked down and working.

Cheers.

S>.