1 2 3 Previous Next 40 Replies Latest reply on Sep 13, 2012 5:57 PM by davidanders

    How to set a portal from multiple tables?

    CraigJulian

      Title

      How to set a portal from multiple tables?

      Post

      I apologize if this is too much information but I am still learning and want to give enough information.

      I have 7 tables (Companies, Contacts, CD Notes, CSG Notes, Company News Notes, Legal Notes & Attachments) and want to have a tab with a "Journal" portal that would scroll all the notes from the listed tables. My IT guys are not much help on this as they refer to SQL and say "Query" but I am not finding a query for FM.

      We I try to build a portal I notice it will only let me pull from one table...any suggestions would be appreciated.

      Craig

      relation.png

        • 1. Re: How to set a portal from multiple tables?
          philmodjunk

          "Query" in SQL roughly corresponds with performing a find in FileMaker. It also describes what happens when you place a portal to a related table on your layout as the relationship you define for it in Manage | Database | Relationships controls what records from the related table will be accessible.

          Not only have you not given "too much info". A bit more would have helped. You haven't indicated how you've related your tables to each other--particularly the "notes" tables.  Please Help Us to Help You...

          You can include fields from more than one table in the same portal. Whether this actually works for you, however, will depend on the relationships you define to link your tables.

          A general observation that I can make. You 4 "notes" tables. It may be that you only need one table for all your notes with fields inside the table documenting the type of note and to which note in which table the note should be linked. Such a "combined notes" table might be easier to include in your "journal" portal.

          • 2. Re: How to set a portal from multiple tables?
            CraigJulian

            PhilModJunk -

            Thanks for the quick response.

            (I am using a Mac with FM Pro Advanced 11 and FileMaker Server. I am very new to this program and learned enough in SalesForce to get me into trouble.)

            I have 4 departments that want to go directly to their notes section (tabs) and note have to scroll through the other departments. Management want a "journal" tab that would compile all the entries.

            Craig

            • 3. Re: How to set a portal from multiple tables?
              philmodjunk

              You can certainly combine Legal Notes and Company New Notes into the same table. You can add a field that identifies which are which.

              You can use filtered portals that filter on the note type and that use an auto-enter calculation to automatically label new notes records by the notes used to create the note record. That way each user group can have their own tab to their own notes portal.

              The "Journal" then becomes an unfiltered portal listing the same notes so that they are all shown together. A Journal on a layout based on Companies would then list all such notes for the current company record. A list view layout based on this combined notes table could list notes for multiple companies grouped under sub summary headers that list the company for which they are connected.

              It will not be as easy to combine CSG notes with the others in the same table as they are linked to contacts and your relationship graph indicates that you may have more than one contact record for a given company. Do these notes also have to be included in the same "journal"?

              • 4. Re: How to set a portal from multiple tables?
                CraigJulian

                PhilModJunk-

                I have to make a change: Currently I have only two layouts - Company - Contacts. Everything is currently viewed from the Company page. The "Company News Notes" & "Legal Notes" are accessed and viewed from the Company layout. The "CD Notes" & "CSG Notes" are added from the Contacts layout and viewed in a portal on the Company page.

                I am going to add the "CSG Notes" back to the Company page just as I have with the "Company News" & "Legal Notes".

                I will change my relationship picture to the first post once I have this completed.

                It will not be as easy to combine CSG notes with the others in the same table as they are linked to contacts and your relationship graph indicates that you may have more than one contact record for a given company. Do these notes also have to be included in the same "journal"? The CSG Notes will now be separated like the Legal and Company News Notes but - I will need the CD Notes on the "Journal" and they are linked to the Contacts.

                Thanks,

                Craig

                • 5. Re: How to set a portal from multiple tables?
                  CraigJulian

                  PhilMod-

                  I have made the changes and now just have a "Notes" table. I have my portal set on the company page and told it to filter (CSG 1; Comp News 2; Legal 3). 

                  I think the issue now is in my scripting...when I click to Add a Note on the Legal tab it automatically flips me to the Company News Note text block. If you click on the Legal tab a new timestamped text block is there as well as in the journal. The same thing happens when you click the Add a Note on the CSG page.

                  I am attaching my Relationships diagram and my scripts to the original post as I can figure out to attach it here. Thanks again and sorry to be a pain.

                  • 6. Re: How to set a portal from multiple tables?
                    philmodjunk

                    The key is to use portal filtering to exclude notes from a given portal that are from another source and then to set up your script so that each new note is labeled automatically. I'm going to limit this example to just Company and legal notes:

                    Your Notes table needs at least these fields:
                    _kf_ID_Company
                    NoteText
                    NoteSource

                    NoteText contains the actual text of your note. NoteSource will have either "Company" or "Legal" in it to distinguish the source of the note.

                    In the tab for your Legal notes, give the portal this portal filter:

                    Notes::NoteSource = "Legal"

                    Use the same method on the Company notes tab, but specify "Company"

                    Now we just need to automatically add "legal" to legal notes and "company" to company notes so that the user does not have to do this.

                    If you use your "add note script", this script can automatically add the appropriate label:

                    #This script should be run from a Companies layout
                    Freeze Window
                    Set Variable [$TabObjectName ; GetValue ( Get ( ScriptParameter ) ; 1 )
                    Set Variable [$NoteSource ; GetValue ( Get ( ScriptParameter ) ; 2 )
                    Set Variable [$CompanyID ; Value: Companies::_kp_ID_Company]
                    Go To Layout [notes]
                    New record/Request
                    Set Field [Notes::_kf_ID_Company ; $CompanyID]
                    Set Field [Notes::NoteSource ; $NoteSource ]
                    Go To Layout [original layout]
                    Go To Object [$TabObjectName] // puts you back on the same tab where you started

                    How to set up the script parameters

                    This script works from two buttons, one on each tab. It passes the object name of the tab and the text to be entered as the notesource in a list of values. Thus, the same script can create either a "Company" or a "Legal" note. First use the Name box in the Inspector's Position tab to name the legal notes tab "Legal Notes" and the Company notes tab "Company Notes". Then, the button on the legal notes tab can pass this expression for the script parameter: List ( "Legal Notes" ; "Legal" ). On the company notes tab, use List ( "Company Notes" ; "Company")

                    • 7. Re: How to set a portal from multiple tables?
                      CraigJulian

                      I have added NoteSource in place of Note Type. I was using Note Type (Number) before.

                      I am sorry for being dense here but are you saying that I have to create separate fields in the Notes table for Note Source? (ie: NoteSource_Legal and NoteSource_CompanyNotes?

                      I have attached the scripts I had before changing to try this...

                      • 8. Re: How to set a portal from multiple tables?
                        philmodjunk

                        No, just one field with different values entered.

                        Here's a demo file you may want to examine: http://www.4shared.com/file/hClMwbqn/SharedNotesTableDemo.html

                        I added a few more lines of code and gave the legal notes and company notes portals their own object names so that the script can place the cursor inside the note text field of the newly added record.

                        • 9. Re: How to set a portal from multiple tables?
                          CraigJulian

                          Thanks for your help and I tried to make that work with no avail. The problem I am going to have with the layout you sent is that I have to show the notes on each tab for each department. The Journal is to grab all the notes from each tab and show them in order of entry by time stamp.

                          I cannot have only one note field.

                          Thanks for trying...it may not be accomplished but I am not giving up.

                          Craig

                          • 10. Re: How to set a portal from multiple tables?
                            philmodjunk

                             The problem I am going to have with the layout you sent is that I have to show the notes on each tab for each department.

                            In the demo file, you have one tab for "legal" and one tab for "company". Doesn't that match what the above sentence describes?

                            This is not the only way to do this. These portals that I put on different tabs can be put on completely different layouts as long as each layout is based on company.

                            The Journal is to grab all the notes from each tab and show them in order of entry by time stamp.

                            The journal in the  journal tab combines the notes from the two example portals in a single portal by not having a filter on the portal. If you were to add a time stamp field to your notes table, the portal can be set to sort the records in order by that time stamp.

                            I cannot have only one note field.

                            Please explain what you mean by that. In the demo file, you have an unlimited number of note records which can be sourced from any number of different departments so that they can be combined into a single "journal".

                            • 11. Re: How to set a portal from multiple tables?
                              CraigJulian

                              Sorry for the confusion again - 

                              In the demo file, you have one tab for "legal" and one tab for "company". Doesn't that match what the above sentence describes? Yes...my mistake.

                              I have fried myself on this - and therefore am getting sloppy. I followed your sample scripts and portals except for one difference. I used the __kp_ID_Company instead of the ID_Company. I do not think this would have caused the issue but I could be wrong. 

                              I cannot have only one note field.

                              Please explain what you mean by that. In the demo file, you have an unlimited number of note records which can be sourced from any number of different departments so that they can be combined into a single "journal". Again, my input issues are most likely to blame here. Once I completed the changes to my portal and scripts - every Add a Note button would throw me back to the Company page and the journal was putting up blank text fields. I will try again tomorrow with another copy of my Master File that I have not destroyed with trials and errors.

                              Thanks, Craig

                              • 12. Re: How to set a portal from multiple tables?
                                philmodjunk

                                I used the __kp_ID_Company instead of the ID_Company

                                On the surface that's just a name change, but compare the field definitions and options to check for differences. I used a number field with an auto-entered serial number in my demo file as this is the simplest safest way to define a primary key.

                                Once I completed the changes to my portal and scripts - every Add a Note button would throw me back to the Company page and the journal was putting up blank text fields. I will try again tomorrow with another copy of my Master File that I have not destroyed with trials and errors.

                                When you do so, carefully compare it to my demo file which doesn't have this issue. The script takes you back to the same exact starting place, except that it then puts the cursor in the note field of the newly created note record. If there's any part of that demo file that you don't understand, feel free to ask about it and I'll explain further how/why it works the way it does.

                                A button for creating a new notes record isn't the only way to set this up. You mentioned having a button for that, so I went with that idea. It may be possible to set this up so that your user just clicks/tabs into the bottom row of the portal and starts entering text. We'd need to use a different method for entering the notesource text so that the record is correctly filtered, if we took that approach.

                                • 13. Re: How to set a portal from multiple tables?
                                  CraigJulian

                                  PhilMod-

                                  I am trying the file that you sent me (http://www.4shared.com/file/hClMwbqn/SharedNotesTableDemo.html) and I tried to add another tab just so I could get comfortable with what you are suggesting.

                                  I added a CSG tab and followed you script but every time I click the Add a Note button on the new tab it takes me back to the Company Notes tab. If you go back to the CSG tab there is a note text box and it does add it to the journal.

                                  Any clues to where my error could be?

                                  Thanks,

                                  Craig 

                                  • 14. Re: How to set a portal from multiple tables?
                                    philmodjunk

                                    The scripts rely on object names that you set via the name box on the position tab of the inspector. The portals need object names. There are also parameters passed to the scripts in the button setup... For each tab you add, the data in this optional parameter box must change to use the object names you used in creating the new tab and portal.

                                    1 2 3 Previous Next