6 Replies Latest reply on May 3, 2017 5:38 AM by TomHays

    List two fields

    MartinBishop

      What would be a good approach to having a calculation field list both of these fields combined?

       

      SIZE | SKU

      --------------------

      SM | 123456

      MD | 098765

      LG  | 456789

      XL  | 345678

      ---------------------

       

      This is the relationship:

       

      —ITEMS —     — SKUS —     — SIZES —

      __ItemID —>    _fkItemID

                               _fkSizeID  <— __SizeID

                                    SKU

        • 1. Re: List two fields
          beverly

          is this one field with all the data in a "parent" (Item?) record? if so, I might use ExecuteSQL().

          can you post screen shot(s) of your relationships for these tables?

          thank you,

          beverly

          • 2. Re: List two fields
            MartinBishop

            Here is an image

            Screen Shot 2017-05-02 at 3.08.40 PM.png

            • 3. Re: List two fields
              beverly

              Thank you for the screen shot. Do you want a portal list of the SIZE | SKU, or as I asked, one field with text as you have in your example?

              beverly

              • 4. Re: List two fields
                MartinBishop

                Not a portal, just one field listing like example, thanks.

                • 5. Re: List two fields
                  beverly

                  see if this caluclation (or scripted Set Field) works in the Items table field:

                  Let (

                  [ _query = "

                      SELECT Size, SKU

                      FROM Size z

                          JOIN SKUs s

                              ON z.\"__SizesID\" = s.\"_fkSizesID\"

                      WHERE s.\"_fkItemID\" = ?

                      ORDER BY z.SortOrder

                      "

                  ; _header = "SIZE | SKU" & Char(13) & "-------------" & Char(13)

                  ; _result = ExecuteSQL ( _query

                          ; " | " ; Char(13)

                          ; Items::__ItemID // passed from the parent record )

                  ]; If ( _result = "?" ; "" ; _header & _result )

                  )

                  • 6. Re: List two fields
                    TomHays

                    If you are working with an earlier version of FileMaker that does not have ExecuteSQL() you can still do it by adding a field to the SKUs table to generate a single row in the table.

                     

                    Add to SKU table calculated field cSize_SKU with definition

                    Size::Size & " | " & SKU

                     

                    Then make a calculation in the Items table to generate the table.

                    "SIZE | SKU" & "¶" &

                    "--------------------" & "¶" &

                    List(SKUs:cSize_SKU) & "¶" &

                    "--------------------"

                     

                     

                    If you don't plan on changing the labels of the sizes, to improve performance you can make the field cSize_SKU into a stored field with its value auto-entered via a lookup (or an auto-enter calculation).

                     

                    -Tom