12 Replies Latest reply on Jan 22, 2016 7:57 AM by ckbrown

    Highlight portal row in File 1 if second field in File 1 matches records in File 2?

    wendy

      I've come close, but... Trying to highlight a portal row in File 1 only when a field in the current record in File 1 (call it Job) matches the data in a field in a record in File 2 (Call it Text).

       

      The Job file has a Text portal. The only records that display from Text are those for which the first word in the Job_Name field matches the first word in Text_Name records via a relationship:

      Job Name

      ABC Blahblah

       

      so the portal shows matching records from Text:

      ABC Record1

      ABC Record 3

      ABC Record 4

       

      but not CDE Record 2 in Text.

       

      This works fine. However, this relationship can result in a listing of zero to over a dozen records in the portal.  Users often do need to see that whole list. But it would be very helpful if I could highlight a couple records for which another field, "J_Dimensions" in Job, exactly matches the "T_Dimensions" field in Text.

       

      ABC Record1 ...in Text

      ABC Record 3 ...in Text  (Highlighted because Dimensions fields in Job and Text match)

      ABC Record 4 ...in Text

       

      I've played around with conditional formatting (which didn't get me too far), and with a second relationship between the two files based on the Dimensions field, and a gColorFlags field (repetitive field with various colors), and the use of a "Flag Display" field with a calculation and the calculation result as Container. (If JobsDImensions = TextDimensions, GetRepetition(gColorFlags; 7)...

       

      I know just about anything except world peace is possible via Filemaker, and I thought I could figure it out, but I keep getting tangled up--especially over which fields go where--and I'm going in circles. Help?

        • 1. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
          PeterWindle

          You should certainly be able to use conditional formatting within the portal row.

          condition should be applied to the portal row object as:

          if(Job::J_Dimensions=Text::T_Dimensions),1,"")

          with a colour or other attribute as the result so you can see it.

           

          and I believe that you can shorten this to :

          Job::J_Dimensions=Text::T_Dimensions

          although I always prefer to have the whole thing typed out...

           

          There could also be formatting applied at field level into a calculated field within Text and display that field

          • 2. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
            erolst

            PeterWindle wrote:

            and I believe that you can shorten this to :

            Job::J_Dimensions=Text::T_Dimensions

            Absolutely; a Conditional Formatting condition expects a Boolean result. So why say

             

            Case ( somePredicate ; True ; False )

             

            when you can simply state:

             

            somePredicate (that is True or False by itself)

             

            ?

            • 3. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
              rrrichie

              What I have done in the past is use globals like $$CURRENT_JOB_ID or $$SELECTED_JOB_ID then use that global with conditional formatting to do what you want.

               

              You can place an empty text object on a portal row for example and fill the background with the select color.

               

              I give portals and objects needing to update an object name.  Then after selecting a row call a script update layout objects with refresh object script step in it.

               

              By the way in FMP14 and Yosemite it doesn't work I need to do a screen refresh or commit record for some reason. Same layout works fine on FMP13/14 and non-yosemite macs.

               

              Hope that helps you along the way :-)

               

              Just remembered  globals don't work across files... :-)  So that wouldn't work here

              • 4. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                PeterWindle

                why say more when you don't need to? Well, I'm just a little crazy. I like to prove the existence of nothing.

                 

                It's just reassuring for me to see a second fall back value, even if it's not required.

                 

                Not sure why?

                 

                For me, it's like saying if I don't go, I stop. If I don't see stop, then I assume I could go faster or slower instead of stopping.

                • 5. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                  rrrichie

                  yeah sometimes I write $isThisAGoodThing = 1 when I am in a paranoid mood and think FileMaker might mistake $isThisAGoodThing have a value of ? as true :-)

                  • 6. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                    wendy

                    I'm sorry, all, I need a few more details.

                     

                    Peter, when you say "condition should be applied to the portal row object..." please define what you mean by the portal row object.

                     

                    Applying conditional formatting to the portal itself doesn't work because it highlights all entries in the portal. I tried placing the Text::T Dimensions field, to which I apply formatting of text as None and background as a solid light yellow--this doesn't work great because you see white text on a yellow background--but the big problems is all lines in the portal are yellow. That's even though I'm using a relationship between Job and Text where J_Dimensions and T_Dimensions match.

                     

                    rrritchie, I don't believe the script idea will work as this has nothing to do with actually selecting a script row. (Also, are you saying I should be using $$ variables somehow?)

                     

                    The fact that I'm doing this within a portal that already has the name relationship is confusing to me...

                     

                    Thanks...

                    • 7. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                      erolst

                      I'm not sure what you mean by that …

                      wendy wrote:

                      I've played around with conditional formatting (which didn't get me too far)

                       

                      … because it works. Using a flag field works, too. See the attached sample file.

                       

                      PS 1: You're right in that there is no portal row object when it comes to CF; you need to use an existing object or put one in for that purpose to apply CF to.

                       

                      PS 2: While your relationship works, it is usually recommended against using natural – business – data as relationship keys.

                       

                      I'd suggest you relate the Text records (or other child records, for that matter) by a serial, auto-entered, meaningless ID (the primary key that every table* should have) and a foreign key field in the child table; you can then simply display the related Job name (or use a calculation field if you need a Text attribute to go into the respective Text record identifier).


                      Not only will this keep the key in use safe from users, it also allows you to rename a job, should the fancy (or the necessity) strike you, without orphaning any related records, since that name is independent from the actual key.


                      * with more than one record; so you may skip that requirement for something like a utility or dashboard table.

                      • 8. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                        wendy

                        Conditional formatting undoubtedly didn't work because I was doing something wrong!  (Hence this post... )

                         

                        When you say:

                        you need to use an existing object or put one in for that purpose to apply CF to.

                        ...that's where I get stuck because I don't follow what this object should be. Thank you for the sample file; that should help! I will look it over and futz with mine accordingly and let you know how I do...

                         

                        All records in all our files do have serial, auto-generated meaningless numeric keys that are not accessible to users. In this case the portal was set up (by someone else) and the records related via a different field (JobName) in order to show just those records where that data matches. Also, this database has been built learn-as-you-go style from the ground up since before FIlemaker 5, so things aren't always as elegant as we'd like!

                        • 9. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                          erolst

                          wendy wrote:

                          When you say:

                          you need to use an existing object or put one in for that purpose to apply CF to.

                          ...that's where I get stuck because I don't follow what this object should be.

                           

                          Well, it can be any object in the portal.

                           

                          Remember that a field on a layout is an object, too – and that is what you'd usually choose for that purpose; if for whatever reason a field cannot be used, add an object – preferably a dummy button, because you cannot CF the graphical objects/shapes that are added with the graphics tool.

                          • 10. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                            PeterWindle

                            I tried placing the Text::T Dimensions field, to which I apply formatting of text as None and background as a solid light yellow--this doesn't work great because you see white text on a yellow background--but the big problems is all lines in the portal are yellow. That's even though I'm using a relationship between Job and Text where J_Dimensions and T_Dimensions match.

                            ummm, try a different (contrasting) colour as your conditional result, or change the portal background colour to contrast your highlight. You say it does not work great, but does that mean it works, but you can't see it well? If it works, then changing the colours is your solution ;-)

                            • 11. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                              wendy

                              Wow. I was making this much harder than it needed to be. The "dummy button" inserted into the portal, with a simple "dimensions = dimensions" statement, works, as does simply using the text field.

                               

                              I still can't get the method with the calculating FlagColor field--which is very similar to what I was originally trying--to work. That may have something to do with that I have two FILES, not two tables, and perhaps that neither of the two dimensions fields are actually IN the portal. (Users don't need to see the base dimensions; what the portal needs to show are the "officially formatted dimensions" that go on packaging.)

                               

                              I'm very grateful, and hope this post also helps someone else.

                              • 12. Re: Highlight portal row in File 1 if second field in File 1 matches records in File 2?
                                ckbrown

                                I have a similar problem, but this solution isn't working consistently.

                                 

                                for example, I have a fruit table, the fruit is sold individually and in groups.  So I have a groups table and a fruit to groups table.   I also have a fruit to buyer table.

                                 

                                I have portals set up for the buyer table to show the contents of a selected group as well as a portal to add additional fruit individually. I want to highlight a fruit that is selected individually by a buyer when it is also included with a group.

                                 

                                I have tried conditional formatting the fruit name in the individual selection portal with "fruit to buyer fruit FK ID = Fruit to group fruit FK ID"  If there is no fruit group selected, only the blank, "add next portal row" field is highlighted.  On one it does highlight the way I expect, the next buyer record, it doesn't highlight the duplicate.  Any ideas?