6 Replies Latest reply on Aug 30, 2012 4:05 PM by philmodjunk

    Let Function Question

    ShirleyMB

      Title

      Let Function Question

      Post

      I have a related data base of only 10 records which include, record ID, series names A to J, series titles, and series descriptions. A calculation field shows the information in one field (SeriesandDescription). I created a field called “Series All” showing the information from those 10 records using the Let function. When I decided to change any aspect of a series (e.g. switching series G and H or changing the title of one series), the information does not appear in the related data base. (I renumber the record ID and sort by it.) Am I using the wrong function? Here is my calculation for the Series All field:

       

      GetNthRecord (SeriesandDescription ; 1) & "¶¶" &

      GetNthRecord (SeriesandDescription ; 2) & "¶¶" &

      GetNthRecord (SeriesandDescription ; 3 ) & "¶¶" &

      GetNthRecord (SeriesandDescription ; 4 )& "¶¶" &

      GetNthRecord (SeriesandDescription ; 5 )& "¶¶" &

      GetNthRecord (SeriesandDescription ; 6 )& "¶¶" &

      GetNthRecord (SeriesandDescription ; 7 )& "¶¶" &

      GetNthRecord (SeriesandDescription ; 8 )& "¶¶" &

      GetNthRecord (SeriesandDescription ; 9 )& "¶¶" &

      GetNthRecord (SeriesandDescription ; 10 )

       

      Thanks in advance for anyone’s help.

        • 1. Re: Let Function Question
          philmodjunk

          In your example, I don't see any let function and I don't see any relationship. and for some reason, you have double spaced your list.

          If you are using a relationship to match to the records you want to see listed, you can use a much simpler calculation:

          Substitute ( List ( RelatedTable::SeriesandDescription ) ; ¶ ;"¶¶" )

          The substitute takes the single spaced list of values and doublespaces it.

          But also consider that a portal to the relatetable can also list these series without any calculation needed at all.

          For FMP 12 users, the ExecuteSQL function is also an option that can be used to list these values in a single field and no relationship need be defined for it in the relationships graph.

          • 2. Re: Let Function Question
            ShirleyMB

            I had meant the Get function. The double-spacing was only because I had typed my question in Word so as to be better able to formulate my thoughts, and I then pasted that into the forum question. No spaces exist in my formula. As to the relationship, it was based on the record ID. I'll try the methods you suggested. As always, PhilModJunk, you're very speedy and helpful -- much appreciated!

            • 3. Re: Let Function Question
              ShirleyMB

              I had omitted a vital part of my question. The information from the 10 records is meant to appear as 1 text block in a trailing grand summary in the main data base for export as a PDF. Series A with its title and description is followed by Series B. etc. That's why I used the GetNth function above with paragraph marks appearing after each series section. However, any changes made in the series data base do not show up in the related data base.

              • 4. Re: Let Function Question
                philmodjunk

                Your text IS double spaced as you are using a pair of returns to separate each value: "¶¶" instead of just ¶.

                You can use either a list calculation, a portal or a calclation field with ExecuteSQL to produce this list in your grand summary layout part and it can still be saved as a PDF.

                GetNthRecord, (Not Get--that's a different function Wink) could be used as well, but there are details as to the context that appear missing so I can't debug that option with you and the results are still much more complex than using one of the other options.

                If you want to figure out why GetNthRecord didn't work, you'll need to fill in those missing details.

                What relationship are you talking about? Is the layout for your report based on one table and this data is from a related table? If so, please describe the relationship in detail or post a screen shot of the relevant portion of your relationship graph.

                I can note, that "context" will make a big difference in how GetNthRecord evalulates. If GetNthRecord references a field in a related table, it refers to the "Nth" related record. Picture a portal based on that relationship and GetNthRecord can reference data from different rows in it.

                If it refers to a field defined in the same record as the calculation using GetNthRecord, the current found set and sort order determine which record is returned if the field is placed on a layout based on the same table. If you reference the field from the context of a related table, it refers to the set of related records and the relationship's sort order can affect which record is referenced by the function.

                And this function almost always has to be defined as an Unstored calcualtion and not used as an auto-entered calculation in order to produce results that update when the context and/or referenced records are updated.

                • 5. Re: Let Function Question
                  ShirleyMB

                  The GetNthrecord calculation works perfectly in the series data base. It's only when I place the field in the related data base that the data appear incorrectly. The only relationship I made was to Series letter (therefore no need for a screen shot of the relationship).

                  • 6. Re: Let Function Question
                    philmodjunk

                    Why not just add a 10 row portal to the series table to the grand summary part on your layout?