9 Replies Latest reply on May 23, 2014 9:40 AM by philmodjunk

    How to get multiple results into one field

    dinoapolito

      Title

      How to get multiple results into one field

      Post

           Hi,

           I have a 'Customers' table with a Portal on it to a related Orders table. On CustomerID can have many orders.

           I then have a Products table for each order. One OrderID can have many products.

           In the Orders portal I would like to display the Products for a single order all on a single row. For example if orderID 666 has products AB, BB and CX the portal would show (amongst other things)

           666       AB BB CX

           How can I do this?

           I have tried to make a calculation field in the orders table that uses the ExecuteSQL fuction to return the results on a single line.  It works OK on my test database with a few thousand records but is unusable in the real world as the Products table has over 1 million records and the function hangs for half an hour before I kill the process.

           Is there another way to achieve this?

           Many thanks

           Dino

            

            

            

            

            

            

        • 1. Re: How to get multiple results into one field
          philmodjunk

               You appear to be missing a table:

               Customer----<Order-----<LineItems>-----Products.

               A portal to Line items would be used to build the list of items purchased on a given order.

               Once you have that, you can display the products from a given order in Three different ways:

                 
          1.           a "horizontal portal" to LineItems can be set up. This is a row of single row portals to line items where the first portal specifies "initial row: 1", The next specifies "initial row: 2" and so forth...
          2.      
          3.           Substitute ( LineItems::Field ; ¶ ; " " ) can list data from LineItems in a single horizontal row in a field. The field you refer to can be a calculation field that combines data from different fields in LineItems should that be desirable.
          4.      
          5.           ExecuteSQL (FileMaker 12 or newer) is a function that can pull data from multiple fields and records of LineItems into a single row of text.
          • 2. Re: How to get multiple results into one field
            dinoapolito

                 Thanks for the suggestions. I already tried and dismissed the Execute SQL option as it was TOO slow. 

                 What I ended up doing was creating a calculation field in the Products table that was a concat of the product and the qty ordered (eg 1x PROD1).

                 Then in the Orders Table another calculation uses the List function pointing to the calculation field in Products. I use a substitute to change the delimiter . Like so

                 Substitute ( List (Products::cProducts) ; ¶ ; "/ " )

                 That works, gives me what I want and is heaps faster than using execute SQL. 

                 But it's still slow. I just think Filemaker is not too good at handling 1-2 million records in a table. In Edit>Preferences I've set the memory to the max it lets me - 256MB which seems very low.

                 -dino

                  

                  

            • 3. Re: How to get multiple results into one field
              philmodjunk

                   There are ways to speed this up by quite a bit. A few of my tables in one of my databases have in excess of 2 million records. They require some careful handling, but they can be quite manageable in FileMaker in most cases. You haven't exhausted all your options.

                   The nice thing about Invoices (or orders) is that once the transaction is complete they aren't subject to change except in rare circumstances.

                   Instead of defining a calculation field, for example, either option 1 or 2 can be used in either an auto-entered calculation or in a set field script step evaluated/performed at the time the sales transaction is finalized. this then stores the result in a simple text field that does not have to evaluate each time you pull of a list view of this data.

              • 4. Re: How to get multiple results into one field
                dinoapolito

                     I might be showing my inexperience here but I wanted to explore option 2 in your original reply but don't know how to go about it.

                     I already have the 'LineItems' table - I didn't mention in the OP to keep it simple. Basically the Orders table has a one to many relationship to the OrderItems table. For a given OrderID there may be many OrderItems.

                     But how do I do it without a calculation field? All my data tables are shadow tables from an SQL database. I can add fields to these tables in Filemaker but they have to be calculation fields. I can't have a simple text field in these tables.

                     Also in this particular application orders can be changed regularly enough that we need to allow for those changes.

                      

                      

                • 5. Re: How to get multiple results into one field
                  philmodjunk
                       

                            Also in this particular application orders can be changed regularly enough that we need to allow for those changes.

                       Yes, but you shouldn't allow orders to be changed after product is delivered. In many businesses once product is shipped or payment is received, you do not allow the invoice to be changed. If a change is needed, you void the original and issue a new one. (or issue a supplemental invoice.)

                       That doesn't mean that scripts can be used to update a text field. The text field can be defined in a FileMaker table linked to your shadow table in a one to one relationship. And script triggers and custom menu driven scripts (where your script replaces a standard menu option) can still be used to update such fields each time an event that requires such an update occurs.

                  • 6. Re: How to get multiple results into one field
                    dinoapolito

                         The orders here are not orders in the common sense of the word you're thinking of. I used it  in that sense to  help people understand my original post..There is no invoice for example.. Instead these orders are  generated by other software for items in a laboratory that go through various stages of production  before completion. Most times the orders don''t change  although they may. And the status  will change in case I  want to report on that.

                         But id still like to investigate alternatives to my calculation field because it is slow.. however I'm not sure how your suggestion of a Filemaker table would be quicker. Are you suggesting this table be populated with all  1.5 million records  and i then report on that? Wouldn't that action in itself be slow and time consuming?

                         Or do you mean that table gets inserted into as needed? But again that would mean querying the shadow table with its millions of records in order to do an insert into the local table.

                    • 7. Re: How to get multiple results into one field
                      philmodjunk

                           The more I think about this, I'm not sure that I have a suggestion that's truly workable. My original concept was to capture each event that changed an individual record and perform a script that updates a simple data field in order to keep that field of that one record up to date, but I wasn't taking into consideration that these changes are not taking place inside FileMaker, but rather in the remote data source from which your shadow tables are querying data.

                           Thus, other than limiting your data sets as much as possible, I don't really see much you can do here to get this to speed up.

                      • 8. Re: How to get multiple results into one field
                        dinoapolito

                             I wouldn't worry about the data changing because I am already handling that with scripts that call a refresh procedure on the external database. The user can perform a refresh on demand or more commonly first thing in the morning or last thing at night. (A full refresh can take 20 minutes because of the volume of data and the hookey calculations it does on tables with up to seven joins.)

                             Then their reports are up to date for all but anything that may have changed since their last refresh. But they know that and know when to refresh. It's unlikely, for example, that in the afternoon they need to report on data that was created or modified that morning.

                             So forgetting that the data may change I am still keen to explore alternatives to my calculation field that concats a result set into a single field with a delimiter of my choice.

                              

                        • 9. Re: How to get multiple results into one field
                          philmodjunk

                               Ideally, the database software from which FileMaker is using ODBC to access this data would generate and update that data in a field and then FileMaker would simply access that single field as one of the fields in the shadow table. Otherwise, you have a "can't get there quickly from here" issue as the data field would be part of a record in a regular FileMaker table while the newly added/changed data that would be used to update that field is in the tables of your external data source.