10 Replies Latest reply on Jun 16, 2010 4:50 PM by philmodjunk

    Relationship problem

    BobSchwenkler

      Title

      Relationship problem

      Post

      I have a match between two tables, let's say A and B. The layout is based upon A. B is viewed from a portal. Match field for A is a global calculation storing the number 1. Match field for B is an unstored calculation, calculating to 0 or 1. Both calculation fields are calculating to number. This match isn't working for me, no information displayed in the portal.

       

      Changing Bs match field to a number field, the relationship works fine.

       

      Bs field is calculating based on a condition set in table A. I could run a more involved script to set the fields in table B when this condition changes, but if I can just do it with a relationship that would be nice.

       

      Seems like this is one of the relational idiosyncracies of this application, and I'm still learning them.

       

       

        • 1. Re: Relationship problem
          Mystick

          don't know if it would be possible for you, but one solution would be to modify your field in table B.

           

          instead of unstored calculation, create a number field, with auto-entry based on a calculation that replace current value.

           

           

          • 2. Re: Relationship problem
            BobSchwenkler

            This doesn't work as Bs field won't recalculate via the relationship. Or maybe it can and I'm not doing it right?

            • 3. Re: Relationship problem
              philmodjunk

              Bob Swhenkler,

               

              The approach you describe will fail as unstored or global values cannot be used on the "child" side of a relationship such as you need for your portal. (Filemaker needs an index for that field in order to support the relationship and such fields by definition do not have an index.)

               

              Mystick suggests a reasonable option, but be advised that this field will not automatically update when you modify the value of a field in Table A that is referenced by your auto-enter calculation. (Auto-enter calculations with "replace current value" will automatically update only for fields that are part of the same table as the auto-enter field.)

               

              You'll need a method to automate the update of this field whenever a referenced field in Table A is modified. One simple way to do this is to set up script triggers on such fields wherever they may be edited on your layouts. THese triggers can use set field to update this field in Table B.

               

              There may be a better work around, but I'd need to see an real life description of what you are trying to do with real values, tables and fields rather than abstract examples.

              • 4. Re: Relationship problem
                BobSchwenkler

                Cool, thanks. I'll experiment some more with that in mind.

                 

                This particular layout is being designed for IWP, so script triggers won't work, but I've included a refresh type button already, so I can run whatever script I need that way.

                • 5. Re: Relationship problem
                  BobSchwenkler

                  So I'm still having trouble. I'm trying to do this with both fields in the same table, but only one of my records is updating. I'll explain further what I'm trying to do. There might be an easier way.

                   

                  I have a layout based upon what is essentially an Invoice table. There is a portal showing active catalog items (albums) from Inventory table. Match field here is global calculation calculating to 1 in Invoice table, and active records in Inventory table marked 1 (inactive 0).

                   

                  Portal rows can be clicked upon to view more detailed item information in lookup fields next to portal.

                   

                  I want a quick way to filter portal results. Sales rep calls store and wants to find a certain album, or albums by a given band without scrolling through a large portal list. I want them to be able to enter text into a field (below named gFilter Text) and click a "Filter" button to filter portal results.

                   

                  The calculation I'm currently using to create a match based on this input text is

                  If(not IsEmpty(gFilter Text);

                  PatternCount(Album Name;gFilter Text);

                  1)

                   

                  Which calculates to 0> for valid records, or all records =1 if no text is present. Match field on the other side is the same global calculation which =1. (I still haven't gotten to writing in what happens if patternCount returns a value 1>)

                   

                  Hopefully this is a clear enough description. I can create a sample file instead if needed.

                   

                  Thanks.

                  • 6. Re: Relationship problem
                    philmodjunk

                    Ok, toss out everything I've already suggested. There's a much better way to filter your portal and it doesn't require updating a field in your portal records.

                     

                    If you are using filemaker 11, setup a filter expression in portal setup:

                     

                    ( PatternCount(PortalTable::Album Name; LayoutTable::gFilter Text) > 0 ) or IsEmpty ( LayoutTable::gFIlter Text )

                     

                    If you are using an older copy of filemaker, you can get close to this effect by including a couple of special calclulation fields in your portal relationship.

                     

                    LayoutTable::cFilterKeywords: Substitute ( gFilter Text ; " " ; ¶ )

                     

                    PortalTable::cAlbumKeywords: Subsitute ( Album Name; " " ; ¶ )

                     

                    In your relationship, you include this pair of fields with the pairs of fields you've already specified for your relationship.

                     

                    This approach only matches whole words and will match any one word in gFilter Text to any one word in Album Name so it may not work for you.

                     

                    PS. Yeah a SQL style "Like" keyword would be pretty handy here if we could only use it...

                     

                     

                    • 7. Re: Relationship problem
                      BobSchwenkler

                      I'd like to make it so an exact match is not needed. I went ahead and did a loop script that sets the match field values, so that will work for the time being I think.

                       

                      One remaining issue is that the portal is not refreshing. I included a Refresh Window script step that flushes the join cache. The portal updates instantly if I click somewhere else on the layout. Any ideas?

                       

                      Thanks for the help.

                      • 8. Re: Relationship problem
                        philmodjunk

                        What version of filemaker are you using? In filemaker 11, this shouldn't be a problem.

                         

                        In older versions, you may want to script a find based on your filter text and Capture the list of line item ID's storing them in a global text field that is part of the portal relationship. That will give you partial matches as you've indicated you want.

                         

                        You might try commit record as another way to get the window to refresh.

                        • 9. Re: Relationship problem
                          BobSchwenkler

                          FM 10, sorry. Thanks again.

                          • 10. Re: Relationship problem
                            philmodjunk

                            With filemaker 10, try this approach:

                             

                            If you don't have a serial ID field in line items, define one and use replace field contents to give a serial number to existing records--updating auto entry options at the same time.

                             

                            Define a portal relationship like this: (names will differ on yours)

                             

                            Invoices::InvoiceID = LineItems::InvoiceID AND

                            Invoices::gSelectedItems = LineItems::SerialID

                             

                            Now create a new layout, SerialIDs, that has one and only one field on it: LineItems::SerialID (and base this layout on LineItems)

                            Define a new global text field, gSelectedItems and place it on another layout (I'll call it "scratch" here)

                             

                            Your script:

                            Freeze Window

                            Go To Layout ["SerialIDs" (LineItems)

                            Enter Find Mode []

                            Set field [ LineItems::AlbumName ; "*" & Invoices::gFilter Text & "*"]

                            Set Error Capture [on]

                            Perform Find[]

                            Copy All Records

                            Go To Layout ["Scratch" ]

                            Paste [Invoices::gSelectedItems]

                            Go To Layout [original layout]

                            Commit Record // or try refresh window, but I think you need the commit option for web publishing to tell the server to re-send the page