1 2 Previous Next 21 Replies Latest reply on May 14, 2017 3:32 AM by piaccounting

    Portal subsets

    piaccounting

      I would like to display data from three tables on one form but need some help to achieve the desired results.

       

      Table A is to display fields from one record and related Table B will show (as a portal) related records from the table A record. What I would then like to do is display in a third table – related Table C (again as a portal) - is records related to Table B.

       

      However what I would further like to do is to only show records in table C when the user “selects” a record from table B. The records to be shown in table C will be those related to the selected record of table B.

       

      So far my table A and B records are displayed correctly but my table C contains all related records from the displayed table B records.

       

      Is it a question of defining the selection in the portal setup dialog for table C? Although I couldn’t see an obvious calculation to specify records for the chosen table B record.

       

      Is this possible or is there a better alternative?

      Many thanks for any help.

      Dave

        • 1. Re: Portal subsets
          beverly

          When you "select" you set a field with a primary key value. This field can be a global storage. Then create a relationship between the global and your final table. The portal based on this relationship will be "filtered".

          However, should you wish to use a "grandchild" portal (showing all) AND actually filter the portal, the selected value (still placed into the field) can be used in a filter calculation.

          Which method I use may depend upon the number of related grandchildren I have and if I want to ever show them all for some reason.

           

          Sent from miPhone

          1 of 1 people found this helpful
          • 2. Re: Portal subsets
            philmodjunk

            You appear to have these tables/relationships:

            Table A-----<Table B---<Table C

            Table B::__pkTableBID = Table C::_fkTableBID

             

            Make a new occurrence of Table C (button with two plus signs) and link it directly to table A like this:

             

            Table A::_fkSelectedTableBID = Table C Selected::_fkTableBID

             

            Delete your portal to Table C and replace it a portal to Table C Selected. Put a button in the portal row of your portal to Table B with this script:

             

            Set Field [ Table A::_fkSelectedTableBID ; Table B::__pkTableBID ]

             

            This button can be transparent and cover the entire portal row if you do not need to edit data in the portal row.

            1 of 1 people found this helpful
            • 3. Re: Portal subsets
              piaccounting

              Thank you for your reply. Had not thought of that idea so will give it a try. In this instance, I do not want the user to have the option of seeing all grandchild records - but there may be an occasion when I want this feature - so it will be a good learning exercise for me - and give me flexibility. Thank you.

              • 4. Re: Portal subsets
                piaccounting

                Thank you for your reply. Had to read it through a few times to get the gist of what you are saying (my slowness - not your explanation).

                I think, if I have understood, (and you are correct in the relationships), is that I link table C directly to table A and then with a transparent button on the entire portal row B when ever it is selected (anywhere on the row) it will set the relationship between table A and B (to that particular row/record) which in turn will establish the correct subset of records from table C. Clever stuff. Now let's see if I can implement what you recommend. I've learned some good things today. Thank you very much.

                Dave

                • 5. Re: Portal subsets
                  philmodjunk

                  That's not quite correct.

                   

                  I suggested linking an occurrence of Table C to Table A. This leaves the current A to B to C relationships unchanged.

                   

                  And clicking the button does not change the relationship between A and B. It links the current record of A to a set of records in C--via that new occurrence of C.

                   

                  A table OCCURRENCE, BTW, is what we call a "box" on the relationship graph. You can create and use multiple occurrences of the same table in order to set up different relationships.

                  • 6. Re: Portal subsets
                    beverly

                    no need to link parent to grandchildren directly. they are "linked" through their common child records.

                     

                    parent::parentID_pk = child::parentID_fk

                              child::childID_pk = grandchild::childID_fk

                     

                    This already allows you to see each other depending on the context of the layout (and it's associated table).

                     

                    if you select a row in a portal "child" that displays on the layout based on "parent", it can set a field with that childID_pk.

                     

                    1. make a new relationship that links the field to the grandchildren (narrows down to just the grandchildren who have that child as a parent)

                    OR

                    2. make the portal on the "child" layout that shows grandchildren and filter based on the field. with no selection, all grandchildren related to the parent (grandparent).

                    beverly

                    1 of 1 people found this helpful
                    • 7. Re: Portal subsets
                      piaccounting

                      Table A::_fkSelectedTableBID = Table C Selected::_fkTableBID

                       

                      Minor problem (hopefully) to try and resolve.

                       

                      Table B is a many to one relation to Table A. Table C can link to table A through table B but not directly. So I do not have a "natural" “Table A::_fkSelectedTableBID” value.

                       

                      The "natural" value to place in this field would be (I think) the primary key ID field of table B, when the (table B) record is selected. This would then enable table C to directly link to table A, as it is this field which relates table C to table B.

                       

                      Is this possible or sensible, or am I missing something?

                       

                      Thank you.

                      Wrote this before I saw your latest comment - so will revisit. Am okay with concept of the table occurrences.

                       

                      Set Field [ Table A::_fkSelectedTableBID ; Table B::__pkTableBID ]

                      - Ah-ha - I think the penny is dropping. I will get there - hopefully!

                      • 8. Re: Portal subsets
                        philmodjunk

                        You are describing what I have assumed from the beginning. In my notation __pk stands for primary key and _fk stands for foreign key.

                         

                        You do do need to add a field to Table A to use in your link to selected Table C records.

                        • 9. Re: Portal subsets
                          piaccounting

                          Bit of an iterative process for me – as I didn’t fully grasp the purpose behind the set field command – which once I worked out what was missing – realised that is exactly what you were telling me to do. Then Wow!
                          Minor cosmetic issue as haven’t figured out how to make the button transparent – so it’s a little visible button at the moment .

                           

                          Beginning to like this software very much.

                          Thank you.

                          Will learn Beverly's suggestion next!

                          • 10. Re: Portal subsets
                            philmodjunk

                            You can use the appearance tab to specify no fill, no border and no external shadow.

                             

                            Or you can use the rectangle tool to create a transparent rectangle and then use the format menu to format it as a button.

                             

                            Or you can just select all the fields in the portal row and with them selected, use format as button to turn the group into a button.

                            1 of 1 people found this helpful
                            • 11. Re: Portal subsets
                              piaccounting

                              Thank you for your guidance. I experimented with all three options. Initially the pure button option hid the row when selected (used the fill box with red line through). However on further experimenting placing the fields on top of the button (as opposed to button of top of fields) seems to give the desired results. Although not fully sure why the former method didn't work.

                              The second option works perfectly. The third option also works perfectly – does mean any gapping between the fields does not trigger the script – easy to solve but useful to know.

                               

                              Thank you for your assistance and patience.

                              • 12. Re: Portal subsets
                                piaccounting

                                Hi Beverly,

                                As I have expected reading through the Filemaker training course is one thing – putting the ideas into practice is another – and my next goal!

                                 

                                Am I right in thinking that the global field is capturing the appropriate key from the selected record of table B (child); then in the portal set up for table C (grandchild) you use the “filter portal records” to specify table C records meet the condition?

                                 

                                If so, should the setting of the global field be in a script triggered by a button on the portal row of table B (similar to philmodjunk)?

                                 

                                Many thanks

                                 

                                Dave

                                • 13. Re: Portal subsets
                                  beverly

                                  You can "set" the global field (in "A"), by button (in portal row) & this button can be a small button or a large 'button' (transparent over-lay, covering the entire row).

                                   

                                  Yes, set the field with the primary key of the row/record in "B" and that relates to the foreign key in "C". What you do with it is optional:

                                   

                                  1. create a relationship between the global field and "C" and the portal will be ONLY those that match (or empty if the global field is empty)

                                   

                                  OR

                                   

                                  2. There is already the "tunnel-through" effect of "A" to "C" which would already be able to show all grandchildren of "A" in a portal with the "C". If you desire to "filter" the portal with your selection (from "B"), then make the filter calculation in this portal:

                                  C::bID_fk = <<your global field>>

                                  So that ONLY the 'selected' grandchildren will be shown.

                                   

                                  If you want this to be able to show all the grandchildren if the global is empty (cleared):

                                  Case

                                       ( IsEmpty(<<your global field>>) ; 1

                                       ; C::bID_fk = <<your global field>> ; 1

                                  >     ; "" )

                                   

                                  This allows several choices and a related grandchild will show if the choice results in "1" (true).

                                   

                                  beverly

                                  p.s. reminder that which I do (1 or 2) may depend on several factors.

                                  • 14. Re: Portal subsets
                                    piaccounting

                                    Hello Beverly

                                    Sorry for the delay in updating but had a few diversions from this task. However have been back onto it – determined to get your solution to work.

                                     

                                    Currently the filter on table C is not working as hoped (although guess it's doing what it's told ):

                                     

                                    Table A: Customers

                                    Table B: SalesHdr

                                    Table C: SalesData

                                     

                                    Script behind button on Table B portal record/row:

                                    Set Variable [$$InvoiceMatch; Value: SalesHdr::_pkInvoiceID]

                                     

                                    Filter on Table C portal:

                                    SalesData::_fkInvoiceID = $$InvoiceMatch

                                     

                                    Relationship:

                                    Table B _pkInvoiceID joins to Table C _fkInvoiceID

                                     

                                    If the filter is switched off from Table C then all records appear in the Table C portal as expected. However if I switch the filtering on then currently no records are being displayed.

                                     

                                    I have checked the debugger and the script is being triggered (although not all the time – but an issue for another time) and the global variable is (now) being updated too.

                                     

                                    Am I missing something, please?

                                    Many thanks for your kind assistance again.

                                    Dave

                                    1 2 Previous Next