7 Replies Latest reply on Mar 30, 2016 3:52 AM by electon

    Get portal row number from cartesian join relationship type.

    stephancasas

      Here's a weird one:

       

      For the context of my situation, I have two tables setup:

       

      parentRecords

      Field NameDescription
      __pkRecordIDSerialized number
      _ctJoinAuto-Enter, Global = 0
      calculatedText

      Unstored, = cat0 & <linebreak> & cat1

      cat0Text field
      cat1Text field

       

      lineItems

      Field NameDescription
      _ctJoinAuto-Enter, Global = 0
      recordNumberUnstored, = Get(RecordNumber)
      lineItemTextUnstored, = GetValue(parentRecords::calculatedText; recordNumber)

       

      The two tables are related using the _ctJoin fields to create a Cartesian type join between every record. What I'm trying to do is create two records in the lineItems table that, when shown in a portal from the context of parentRecords, will display the individual lines of data stored in parentRecords::calculatedText. However, I'm finding this increasingly difficult to do. It will work perfectly fine and grab data when only one record exists in parentRecords but fails to supply data for any other records created. In my head I understand, perfectly, why this behaviour is happening. I guess what I'm asking is, "does anyone have a better solution or way to do this?"

       

      Is it at all possible to retrieve just the portal row number from a calculation without requesting Get(RecordNumber)? This would basically solve my issue.

       

      Also, this is my first post to these forums, please advise if I have done something incorrectly.

       

      Cheers,
      Stephan

        • 1. Re: Get portal row number from cartesian join relationship type.
          nicolai

          It looks like you are relating by two _ctJoin fields and both of them are global. FileMaker will need a field (foreign key) on one side of the relationship to be indexed for any relationship to work.

           

          There is an easier way to create a cartesian relationship. On Manage Database?relationship click on the box on the relationship link and instead of "=" select "X".

           

          The completely different question is why you are using cartesian instead of standard linking by key pairs?

           

          If you link by key pair and you have a portal on the layout, this should work.

           

          Nicolai

          • 2. Re: Get portal row number from cartesian join relationship type.
            erolst

            stephancasas wrote:

            In my head I understand, perfectly, why this behaviour is happening.

             

            Right; your child records are simply grabbing the field value from the first record they see via the Cartesian relationship – which will always remain the same one for all children.

             

            Of course, if this is perfectly understandable, why call it “weird” …?

             

            Question is: what is your real-world scenario, i.e. why do this at all? (As a quick idea: create a global field and use a trigger to copy the record data into that field; let the child records reference that global).

             

            btw, you don't need dedicated fields to create a Cartesian relationship; match any two fields using the Cartesian ( x ) operator (you could even delete them later, and the relationship would still work); this is a case where matching the primary key form every table makes sense (since you are bound to always have these).

            • 3. Re: Get portal row number from cartesian join relationship type.
              stephancasas

              The real world scenario is this:

               

              I'm working on a quoting/invoicing system where we need a dynamic way to check for mistakes in data-entry or special conditions that may have to be met for a specific reasons.

               

              To accomplish this, I've created a table (definedConditions) with a few fields:

              • _pkRecordID
              • literalCondition - Specifies a conditional statement.
              • literalBulletin - Explains what is wrong if the conditional statement is met.
              • literalFlag - Specifies a value of 1 or 2; where 1 is "error" and 2 is "warning"
              • literalFlagCalculation = "If(" & literalCondition & "; \"" & literalFlag & "\";" & "\"\"" & ")"
              • listedFlagCalculation = literalFlagCalculation & " & "
              • literalBulletinCalculation = "If(" & literalCondition & "; \"" & Case(literalFlag = 1; "WARNING"; literalFlag = 2; "  ERROR") & ": " & literalBulletin & "\¶\"" & ";" & "\"\"" & ")"
              • listedBulletinCalculation = literalBulletinCalculation & " & "

               

              Then, from the context of the quote/invoice table I've defined this field:

              • listBulletinCalculations = List(definedConditions::listedBulletinCalculation) & "\"\""
              • evalBulletinCalculations = Evaluate(listBulletinCalculations)

               

              So what I end up with is a list of error messages separated by paragraph/line-break characters that show up if one of the conditions in definedConditions is met. It works great, but I've been asked if I can color-code the individual lines according to their interpretation as an error or warning. I can't think of any other way to do this other than a portal.

               

              I'm trying to avoid using any type of script steps because it kind of defeats the idea behind the whole dynamic nature of the framework I've built.

               

              I can get the individual lines out of the evalBulletinCalculations field using a GetValue() calculation, but I can't figure out a way to get that information into a portal so that I can accomplish the color-coding that I've been asked to do.

               

              Also, the implementation of the _ctJoin field, is an old habit I just need to get around to breaking.

               

              Any thoughts on the above scenario are greatly appreciated!

              • 4. Re: Get portal row number from cartesian join relationship type.
                user19752

                There is TextColor() function to change color of any part of text string.

                • 5. Re: Get portal row number from cartesian join relationship type.
                  stephancasas

                  I've come up with a solution, but it's not pretty!

                   

                  Essentially, what I've done is kept the lineItems table from my original example. But, in the GetValue() calculation, I've re-written it as GetValue($$varListedMessages; Get(RecordNumber)). Then, in the parent table for the quotes/invoices, I've defined a calculation field with the following un-stored calculation:

                   

                  • Let([variableName = "$$varListedMessages"; variableValue = evalBulletinCalculations; evalFormula = "Let(variableName = variableValue; \"1\")"]; Evaluate(Substitute(evalFormula;["variableName"; variableName]; ["variableValue"; Quote(variableValue)])))

                   

                  Then, on the layout that displays the line items in the portal, I placed a hidden object that references the calculation field above. Whenever the value in evalBulletinCalculations changes, it forces the above calculation to re-calculate and thus, redefines the $$varListedMessages global variable in the process with updated information. All that has to be done then, is a refresh/flush-cached-join-results and the information will update in the portal exactly as I want it to.

                   

                  Now, if I could just magically get out of having to refresh the layout...

                  • 6. Re: Get portal row number from cartesian join relationship type.
                    erolst

                    stephancasas wrote:

                    Now, if I could just magically get out of having to refresh the layout...

                     

                     

                    You could just store the messages as related records in the first place …

                    • 7. Re: Get portal row number from cartesian join relationship type.
                      electon

                      erolst wrote:

                      btw, you don't need dedicated fields to create a Cartesian relationship; match any two fields using the Cartesian ( x ) operator (you could even delete them later, and the relationship would still work); this is a case where matching the primary key form every table makes sense (since you are bound to always have these).

                      That's a very neat tip!

                      One other thing to watch for is:

                      There absolutely must be at least one record ( in the current found set ) on the parent side of the relationship.

                      Otherwise even a cartesian join will not show any related child records.

                       

                      Just wanted to chime in.