13 Replies Latest reply on Sep 23, 2014 3:07 AM by NickLawrence

    Linking layouts using a portal

    NickLawrence

      Title

      Linking layouts using a portal

      Post

           Hi all,

            

           Probably not the best title for what I'm trying to do, but I have a portal on a layout (layout a) and I have another layout (layout b) which contains the details of the portal on layout a, I'm trying to link these together using a field on both of them Contract Number. I did a similar thin using and Auto Enter Calculation (replacing existing value) the calculation looks like this $$Po_Number. Must have got this from someone off this forum in the past but as it was such a while ago I can't remember what I did. I have tried a similar formula to see if it worked but it doesn't. So I have two fields, both called Contract No, I have set the 'Contract No' field in layout b to an auto enter calculation with the following $$Contract_No but it doesn't seem to be doing anything so I think I may have missed something.

           Basically I'm trying to keep the information in layout b linked by using the Contract No field to tie them together.

            

           Any help greatly appreciated.

            

           Many thanks

            

           Nick

        • 1. Re: Linking layouts using a portal
          philmodjunk

               Do you mean that if you go to Layout B and create a new record you want it to be automatically linked to the current parent record on Layout A?

               If so, your auto-enter calculation can work, but only if you first assign the correct value to the specified global variable. That is typically done by setting up the OnRecordLoad trigger on Layout A with a single step Set Variable script that assigns the current value of the Layout A's record's primary key to this variable. Then any new record created on Layout B will automatically receive this value via the auto-enter calculation.

               Please note that with this design, you have to keep users from going directly to Layout B without first visiting Layout A at least once in order to initialize this variable.

               But if you want to click a button in a portal row of a portal to Layout B's table and pull up that record on Layout B, this can be done with Go To Related Records and does not use an auto-enter calculation.

               And if you use the portal on Layout A to create records in Layout B's table, you also do not need the auto-enter calculation.

          • 2. Re: Linking layouts using a portal
            NickLawrence

                 Layout B is never visited, it just holds the details of the portal in layout a, a new record is created in layout b when the user clicks on a new line in the portal.

                  

                 When the user click on a new line in the portal in layout a, this creates a new record in layout b, trouble is I have nothing grouping these together in layout b, wanted to use the contract no to do this, I did or rather was probably told how to do this for a previous layout but can't remember how. So what I want to happen is.

                  

                 Click on new row in the portal in layout a, a new record is created in layout b that uses the same reference as in the contract no field in layout a (this is manually entered in layout a and not a serial number), the same thing happens for each portal row.

                  

                 Many thanks

                  

                 Nick

            • 3. Re: Linking layouts using a portal
              philmodjunk

                   Ok, then you don't need the global variable and you don't need an auto-enter calculation. If "allow creation of records via this relationship" is enabled for the portal's table occurrence in the relationship set up for this portal, you just enter data into the blank "add row" of the portal and a new record is both created and linked to the current record on Layout A.

                   But I am unclear about what exactly you wan to happen next. I can describe how to set up Layout B as a "detail view" of a given portal record where a button in portal row takes you to that layout and displays the record from the portal row or I can tell you how to set up that layout as a list view where all the records in the portal are shown--or even all the records in the table, but grouped under "sub headers" with data from the Layout A record that is linked to each such group of portal records.

                   But you said "layout B is never visited"?

              • 4. Re: Linking layouts using a portal
                NickLawrence

                     Hi Phil,

                      

                     By general users it is not, it is by myself so I would like it to be in a list or table view sorted in to sub headers with the contract number being the header that would be great.

                      

                     Thanks Phil

                • 5. Re: Linking layouts using a portal
                  philmodjunk

                       Make it a list view. if there is any chance that you will use this layout at some time as a printed report or for use by general users. I'll describe that approach here, but there are similar techniques that can be used with a table view to get sub headers for each group.

                       Add a sub summary layout part "when sorted by" the foreign key field that links these records to the parent table from Layout A. You can put any fields that you desire from the parent record into this sub summary layout part.

                       When viewing records always be careful to sort the records by this field or the sub summary layout part will not be visible.

                       With this layout design, you can show All records and then sort to see all the records in the table grouped by parent record. You will also be able to easily spot any "orphans" that are not linked to any parent record. You can also perform a find and then sort to work with a subset of these records.

                       There are similar "group by" options that you can select for a table view from the foreign key field's column header.

                  • 6. Re: Linking layouts using a portal
                    NickLawrence

                         Hi Phil,

                          

                         At this stage, my primary mission is to get the contract number from the Contract No field in layout a to the Contract No field on layout b, whether it is in table or list, I'm happy to go with what ever is easier.

                          

                         Many thanks

                          

                          

                          

                         Nick

                    • 7. Re: Linking layouts using a portal
                      philmodjunk

                           Key detail here: Layouts are NOT tables. You need to keep that fact in mind. Any number of layouts can all be based on the same table (Table occurrence actually). And any one layout can display data from any number of tables via the relationships that link the layout's table occurrence (as specified in Show Records From in Layout Setup) to other table occurrences.

                           So to show the Contract number from Layout A's table on Layout B, you need only do the following, provided that you have a relationship correctly defined and the correct match field values in place (and this happens automatically when you use a portal to create related records):

                           Enter Layout Mode on Layout B.

                           Drag and drop the field tool onto your layout or open the Field Picker.

                           Select the name of the table occurrence on which Layout A is based from the drop down at the top of either the specify fields or Field Picker dialogs. Then click the Contract Number field to select it. (Now drag and drop if you are using the Field Picker [a new feature of FileMaker 13.])

                           You've now placed the field from Layout A's underlying table on Layout B.

                           If you have defined a field for Contract Number in Layout B's table, open Manage | Database | Fields, find this field definition and delete it. It's redundant.

                      • 8. Re: Linking layouts using a portal
                        NickLawrence

                             Thanks Phil,

                              

                             I did that and it does add the contract number, however it also copies all the lines from the previous records portal, so I think I may have something not setup right, maybe the relationship is not right.

                              

                             Regards

                              

                             Nick

                        • 9. Re: Linking layouts using a portal
                          philmodjunk

                               You'll need to explain in detail what you mean by "I did that".

                               Simply adding a field from a related table to a layout  doesn't copy anything nor even displays data from any other field but the field that you added. So I can't quite make out what you mean by that last post...

                               What I am describing, BTW, does not make any change to Layout A where you have your portal to Layout B's table. I am describing a change to Layout B--which should not have any portal at all on it.

                          • 10. Re: Linking layouts using a portal
                            NickLawrence

                                 Hi Phil,

                                  

                                 Having all manner of problems with this layout design at the moment, it seems to be working sporadically and has now started giving an error message "The operation can not be performed because one or more required related records are not available and cannot be created." which seems to be something wrong with the portal.

                                  

                                 Basically where I started was, I copied your instructions on putting the contract number on layout b which is the portal details layout, it kind of worked OK but sometimes when I clicked on a new line in the portal it wouldn't input the details from layout b, also when I created a new record it was created with lines already in the portal, so what I did was deleted all the records I had created as these where just test records from layout a, but this never deleted the records from layout b only some of them, so I then deleted them to give me a fresh start. After I did that I've started getting the above error, for some reason the portal on layout a does not seem to be linked to the details on layout b even though if you check it is? Any ideas what could fix this is?

                                  

                                 Many thanks

                                  

                                 Nick

                            • 11. Re: Linking layouts using a portal
                              philmodjunk

                                   You seem to be expecting results that aren't consistent with how related tables,  portals etc function.

                                   

                                        but sometimes when I clicked on a new line in the portal it wouldn't input the details from layout b,

                                   The portal is on Layout A right? Why would it import ANY data from a record in table B as displayed on Layout B?

                                   And why would you expect it to?

                                   

                                        but this never deleted the records from layout b only some of them

                                   Depending on how you defined your relationships, it might not delete any records from TABLE B as shown on LAYOUT B. (Sorry, but this is a key concept that seems to still be a source of confusion for you here.)

                                   See my next post for a step by step explanation of layouts, tables, relationships and portals.

                              • 12. Re: Linking layouts using a portal
                                philmodjunk

                                     Let's start from a brand new Database File here and walk through the concepts step by step. I'm not going to use any alphabet labeling here as that simply makes it harder to follow the details.

                                     Go to Manage | Database | Tables. Enter "Clients" as the name for a table and click Create. This creates a data source table but without any fields defined in your database. FileMaker also creates a Table Occurrence box on the relationships tab and creates a layout based on this table occurrence in your database file that you can find via Manage | Layouts or by pulling down the layout drop down after first exiting Manage | Database.

                                     FileMaker gives the new Table Occurrence and the layout the same name: "Clients" as the table. But these are not the same thing. They each have a different role in your database.

                                     Now click the Fields tab, select "clients" from the Table drop down if not already selected and create a field: __pkClientID. Select Number as the field type. While this field definition is still selected, click the Options button to open the field options dialog. Click the Auto-enter tab and select the option for this field to auto-enter a serial number. Each time you create a new record, this field will appear with a unique number already entered into it. The first record will show  1, the next 2 and so forth.

                                     Add a text field to this table named "ClientName".

                                     Now return to the Tables tab and create a new table named "Contracts". Click over to Fields and add two fields: Create _fkClientID as a number field. Do NOT select any field options for this field at this time. Then add a field named "ContractName" to this table. Just like what happened with Clients, FileMaker adds identically named table occurrence and layout for you.

                                     Click over to the Relationships tab. You'll see two Table Occurrences, one named Clients and one named Contracts. These are not the actual tables, just handy references that we can use to define the relationship that we need in order to set up a working portal. In fact, we can actually create multiple copies of these two table occurrences and each copy still refers to the same data source tables we created over on the Tables tab. We don't need to do that now, but this is often necessary in order to define the multiple relationships often needed for use in a relational database like FileMaker.

                                     Use your mouse to drag from __pkClientID to _fkClientID to link these two table occurrences in a relationship. Double click the relationship line and select the "allow creation of records via this relationship" option on the Contracts side of this relationship. This is the feature that puts a blank "add row" into our portal and enables us to create new records in Contracts that are automatically linked to the current Client record. Now click the "delete" check box on the same side of the relationship. This is a dangerous option if misused, but by enabling this option here, any time that you delete a client record, FileMaker will automatically delete all contract records that are linked to it.

                                     Now click OK where needed until you have closed the Manage | Database window.

                                     If you are not already on the Clients layout, select that layout from the layout drop down in the status tool bar. Enter Layout mode.

                                     Use the Portal Tool to draw a rectangle on your layout.

                                     Portal Setup... pops up. Select "Contracts" from the "show related records" drop down. You have just selected a Table Occurrence as shown on the Manage | Database | Relationships section. If you had added more table occurrences, they would also appear in this drop down list.

                                     Select these options:

                                     Show vertical scroll bar, use alternate row state, allow deletion of portal records.

                                     Click OK and you'll get a dialog box for selecting fields to place in your portal. Select both the _fkClientID and ContractName fields. We wouldn't normally select the _fk field for the portal, but by doing so, you'll be able to better observe how things work in this exercise.

                                     Now Click OK and return to Browse Mode. If you don't see any record in your layout, select New Record from the Records menu.

                                     Now enter a name for your client in the clientName field. Then Click into the ContractName field in the portal and enter a contract name. This creates a new record in Contracts. You should see the value of __pkClientID automatically copy over to the _fkClientID field in the portal row. The matching values in these two fields are what enables you to link many records in contracts to the same record in Clients. If you create a new Client record, they will get a new and different number in __pkClientID and new records created in the portal will get that value copied over to link them to this second client instead of the first.

                                     After creating a few records for 2 or more clients, Select the Contracts layout. If you click forward and back through the records on the Contracts layout, you should see each and every record that you created in the portal with the same values that you saw in the portal row.

                                     Now enter layout mode and drag and drop from the field tool to your layout in order to add a new field to the layout. Specify Fields will pop up. Select "Clients" from the drop down at the top of this dialog. Then click the ClientName field to select it.

                                     Now enter browse mode and click through your contract records. You should see the client names that you entered into the clients table on the clients layout show on your contracts layout. None of this data is stored in any layout, it's all stored in your tables. And adding this field to your contracts layout does not add it to the contracts table, you are just using the relationship you defined to show the client name from the client record whose _pkClientID value matches the _fkClientID value of the current record.

                                     Once you've added this clientname field, you can switch to Table View as a way to see all your records in Contracts at the same time to make it easier to see all the data currently in this table.

                                Caulkins Consulting, Home of Adventures In FileMaking

                                • 13. Re: Linking layouts using a portal
                                  NickLawrence

                                  Hi Phil,

                                   

                                  Thanks for all your help, as always, you are a fountain of FM knowledge ;-) I managed to sort out the original problem, was something quite simple and silly in the end lol, I was entering a contract number in and this was needed to link the records between databases.

                                  Again, thanks for your help.

                                   

                                  Regards

                                   

                                  Nick