Two Portals On A Form

[laguna92651]

I have two tables a Note Header table and related Note Lines table.

 

I would like to see all of the Note Header records in a portal (or List or Table view).

 

And on the same layout, see all of the related Note Lines in a second portal below the Note Header portal.

 

I would also want to be able to create records in both portals.

[bcooney]

Are the Note Header records related in any way to the Note Lines records? You seem to suggest that.

 

Basically, you have a parent>child>grandchild relationship (You do not tell us the parent entity). You cannot easily show such a hierarchy, although there are sophisticated techniques.

 

One method is to have side-by-side portals. User selects a Note Header record and its unique ID is set to a global field in the parent. Then, using a relationship global->grandchild table by ID, you can create a second portal that shows the selected Note Header's children in the right-hand portal.

http://fmlayoutmode.com/wp/?p=2195

[laguna92651]

The note header is linked to the Note Lines with a foreign key. I'll go through the URL you sent. That pretty much looks like what I was looking for. I just need the Group portal and the Name portal. I guess the link will tell me, what table is the layout based on?

Thanks

[bcooney]

Study the technique and come back with questions. As I said above, you only mention two tables Notes and Note Lines. So, you need to tell us what is the parent; is it Groups?

[laguna92651]

In my example the Header Notes table is the parent.

[bcooney]

You're somewhat contradicting yourself.

"I would like to see all of the Note Header records in a portal (or List or Table view)."

 

If Note Header records are in a portal, then who's the parent? Not Note Header, unless you're designing some sort of interface that calls for a self-join.

 

Please step back and give us more of a description of what you are trying to achieve. Name all the tables, and give us some sample data.

[laguna92651]

Okay I went through and implemented the recommended example. Modifying the example I would have these tables.

 

Session

Client

ClientNotes

 

In my example file the layout is based on the Sessions table the first portal is based on the Client's table and the second portal is based on the ClientsNotes table. The only thing remaining is is to be able to create a new record in the first portal, Clients. The 'Allow Creation of records in this table...' option is grayed out in the Relationship options between the Sessions occurrence and the Clients occurrence. I would upload the file, but can't figure out how to upload a file here.

[bcooney]

Everytime you respond, you mention different tables. It's much easier to answer questions based on actual circumstances. I know you feel that it helps to "simplify" your setup for us, but actually it just makes it more difficult to address your actual needs.

 

You can post a file by zipping it first.

[laguna92651]

Attached is the example file, this file does not let me create new records for Clients. I would like to create new clients from this layout. Thanks for the help.

Al

Selection Poral.zip

[bruceR]

"...from this layout"

 

There are 4 layouts. What exactly do you want to do?

[laguna92651]

On the Client_List layout I want to be able to create new client records, the left most portal.

[laguna92651]

Any help?

[laguna92651]

Got an email that broswift replied, but don't see a new message?

Thanks

Al

[Lee Smith]

The reply was spam and I deleted it.

[Brooks]

It wasn't spam! I was trying to help. Not sure why I got blocked? I downloaded his file and made some modifications to show him how to accomplish what he was trying to do. I couldn't figure out how to attach the file in the reply so I uploaded it through the files as a sample. How do I attach the file without getting blocked again? 

[laguna92651]

Hi Brooks

Thanks for the help, I had the same problem when I initially tried uploading the file. Select the More Reply Options to get the file upload option. Thanks again.

Al

[Brooks]

I sent it to you in a message, but here you go. Hopefully this points you in the right direction. 

Portal Selection.zip

[bruceR]

There are a couple of problems with this file.

 

1. If no client is selected, or the selected client is deleted, then no clients are visible in the portal on the client list layout.

2. If no names are entered in the new client script, the cancel button has no effect and and a new client record is created anyway.

 

The attached mod fixes these problems by modifying the new client script and by pointing the X relation back to the session table.

 

 

Selection Portal.fmp12.zip

[Brooks]

Thanks for catching that! I didn't spend any debugging time on it, just was quickly trying to show him a method. 

[laguna92651]

Thanks guys, that is what I was looking to do. For my own education what is the basic principle? Why do I need the layout to be based on the Session table. All I was trying to accomplish was to show the two portals, Client and Notes, with being able to create new Clients.

[Brooks]

Okay I went through and implemented the recommended example. Modifying the example I would have these tables.

 

Session

Client

ClientNotes

 

In my example file the layout is based on the Sessions table the first portal is based on the Client's table and the second portal is based on the ClientsNotes table. The only thing remaining is is to be able to create a new record in the first portal, Clients. The 'Allow Creation of records in this table...' option is grayed out in the Relationship options between the Sessions occurrence and the Clients occurrence. I would upload the file, but can't figure out how to upload a file here.

This is how you had it designed. That why is was that way.

 

 

a portal is based on  a relationship. If it is a cross join X does not allow creation because there is no fixed relationship linking the tables. If you have a equal join = then you can create new records via the portal. You can not have a = join with the same table in a portal on a layout based on the same table, and see all the records. All you would see is the selected record since TableOccurance1_ID = TableOccurance2_ID. In your example We are not creating records in the client portal because it is a Cross Join X relationship. If you look at the script on the add button it is taking you to the client layout creating a record using the defined values of NameFirst & NameLast, and then returning you to the Sesion layout. The Last set field in the script is to force the client portal to refresh. It is not really necessary in this scenario, but is a habit since I always use Data Separation model DB's. 

[bruceR]

It's your file.

 

Why did you use a sessions table?

[laguna92651]

I think I understand what you did. If I based the layout on the Cilent table I couldn't both portals to work. Using three tables was the only way I could get it to half way work. In general how would you design the layout if:

 

1. Want a parent list and a child list on the layout.

2. Wanted to be able to create a new parent list.

 

It seems to be a problem when the layout is based on the parent and the parent is want to be shown as a list.

[bruceR]

No, it isn't a problem actually. And it would not be different from what you have already done. What makes you think it is a problem? What kinds of problems did you experience? 

[laguna92651]

If I set up a relationship between Clients and Client Notes. Then create a new layout I have to select a table to base the layout on. If I base the layout on Clients and then try to create a portal based on Clients, I can't, Clients is not shown as an available selection for the portal. Am I doing something wrong. I haven't try the suggestions made above yet, I try that join.

[bruceR]

That's kind of circular reasoning.

 

You defined a Session table.

Then you defined a cartesian relation from the session table to the client table.

Then you defined a layout based on the session table and therefore you could choose the  clientX table occurrence to display a client selector portal.

 

If you want the ClientX table occurrence to be connected to the Client table then connect the ClientX table occurrence to the Client table.

Selection Portal2.fmp12.zip

[laguna92651]

Thanks Bruce, for the help and information, I understand now. Have a Merry Christmas.

Al

[laguna92651]

On the Select Client From Portal script, there is a <Table Missing> error on the Set Field command. What should the table be or is this script not being used?

Thanks

[LaRetta]

Hi Al,

 

That script isn't being used.  If you select the Client name in the client portal, you will see a button spec with GTRR (Go To Related Records) being used instead. I assume it was hold-over from other techniques or testing. 

Edited December 14, 2012 by LaRetta

[LaRetta]

Hey Bruce,

 

I thought I should mention a few differences in whether you have checked or unchecked 'Show Only Related Records Match Current Record' in cases such as this since it will work either way.  I might suggest that, in a case with a Home Page where found set or sort order does not matter, leave it unchecked.  Logic says it would be faster unchecked although I have never speed-tested it; maybe you or others have?  Aside from the potential speed difference, there are other differences  In this situation:

 

A)  If you check 'Show Only Related' (match current record only)

1. You lose your existing found set on Clients always.
2. You lose your existing sort order on your found set on Clients always
3. If there is a relational sort on clients_x, it sorts them in that order otherwise leaves them unsorted (this can replace ever sorting this set here).

  If you uncheck 'Show Only Related' (match current record only)

1. You maintain your existing found set on Clients unless you select a Client not in the found set, then it shows all Clients (you lose the found set).
2. You maintain existing sort order on your found set on Clients always and it ignores relational sort on clients_x if it exists (of course), which can be beneficial in some situations)

Of course these behaviors are obvious when you think about them but the resulting record set/sort order might produce unexpected results if someone isn't aware of these behaviors or is newer to FM.

 

ADDED:  The little guy with the sunglasses was originally a B with closed parentheses but seems this software likes the little guy better and I agree. 

Edited December 15, 2012 by LaRetta

[bruceR]

Excellent detail LaRetta, thanks.

 

Updated file attached,with obsolete selector script deleted.

Selection Portal2.fmp12.zip