7 Replies Latest reply on Jun 20, 2017 9:50 AM by philmodjunk

    Distribution list for actives


      Hello good afternoon,
      How can I make a distribution list only for "active" customers?

      I have a Table Clientes(ID, name, email, status); Distribuicao (ID, date, distribuicao); ITEMS (clientID, DistributionsID).

      i built layout based on Distribution table with one portal of Customers and another portal for DstLine table that should received only actives Customers.

      i did copy this script somewhere and i tried to adapt into here like this:

      Screen Shot 2017-06-19 at 22.48.45.png




      If has more than 700 customers.

        • 1. Re: Distribution list for actives

          Easiest method IMO is to use FileMaker's built-in import facilities.


          Write a script to:

          - store the distributionID in a variable

          - go to a Customer layout and find all active customers

          - go to a table occurrence of Distribution

          - define an Import Records step with the table occurrence of the Customer layout where you performed the Find as the source, and this distribution table occurrence as the target.*

          - use Replace Field Contents to set the distribution foreign key for the newly imported records to your $var


          * you need to create a External Data Source for that to work. Just select the veery same file you're working in.


          You could also collect the IDs of all active customers using ExecuteSQL(), or, if neither method appeals to you, do this:


          Set Variable [ $listOfClientID ; List ( Clients::id ) ]

          Set Variable [ $listOfClientStatus ; List ( Client::status ) ]


          Set Variable [ $clientCount ; ValueCount ( $listOfClientID ) ]


            Exit Loop if [ Let ( $i = $i + 1 ; $i > $clientCount ) ]

            If [ GetValue ( $listOfClientStatus ; $i ) = "Active" ]

              New Record/Request

              Set Field [ Distribution::id_customer ; GetValue ( $listOfClientID ; $i ) ]

              ... etc.

            End If

          End Loop

          • 2. Re: Distribution list for actives

            Bits and pieces seem to be missing here. As near as I can tell, you have these relationships:


            Clientes----<Items>-----Distribucions   (---< means "one to many")


            Clientes::ID = Items::ClientID

            Distribucions::ID = Items::DistributionsID


            But then you mention a table (maybe table occurrence) named DstLine but don't say how it is used or how it relates to the other table occurrences.


            It is not clear whether you want your script to create a new record in Distributions or in Clientes or perhaps another table. As written, it creates new records in Distributions.


            So what exactly are you trying to accomplish with this script?

            • 3. Re: Distribution list for actives

              What i am trying to do is in Distribuition Layout, (where i define the date and the content to Customers),

              i want to have a portal of clientes "Actives" in Left bottom and a portal of DstLines right bottom to receive the chosen clients from left portal.

              the choice can be one-by-one or with a button to select all.


              i hope you understand the idea.

              • 4. Re: Distribution list for actives

                Don't understand. DstLines could be a different table or an occurrence of clientes or ???


                And how does the user select clients from the portal if active clients? Do you want to click a record in the left hand portal to see a record appear on the right, have the user click to select a group of records then click a button to see a group of records on the right?

                • 5. Re: Distribution list for actives

                  DstLines is not Occurrence, is normal table. in left hand portal i have a filter portal record set all "active".

                  in the right hand portal is DstLine, will be populated by script in the button "move all".

                  please take a look this to scratch.

                  Screen Shot 2017-06-20 at 14.35.27.png

                  the script to insert one-by-one is done. what if there is more than 100 records?

                  how can i send the all client_ID into DstLines table?


                  Distribuition----<DstLines>---- Clients

                  • 6. Re: Distribution list for actives

                    I do this UI usually by allowing the user to collect whatever entries they want in a global $$var (and provide feedback via Conditional Formatting), then “move” them as a batch (however large that batch may be).


                    This goes together with convenience functions à la "Select all" (empty $$var and set it to List ( Client_active::id ))  or "Select none" (just empty the $$var) from which point one can fine-tune the selection.


                    Once the user clicks “Move”, and the $$var isn't empty, you can use one of the methods described in an earlier post.


                    “Remove all” would mean: delete all related records for this Distribution record.

                    • 7. Re: Distribution list for actives

                      To create new records in the DistLines table this way, you would not do it by creating new records in Distributions--which is what your current record does. To use New Record/Request to create a new record in a specific table requires that your script use go to layout to go to a layout based on an occurrence of that table.


                      There is also another method that you could use that does not require changing layouts: MagicKey. You can web search this term to find some excellent articles on how to set this up and thus not need to change layouts or open an extra window (as is often done with the changing layout method).


                      The basic script for both methods requires:


                      1. Collecting a list of all clients from the first portal. If it is unfiltered, the List function may be used to set a variable to the list of client ID's.
                      2. Loop through the list creating new related records.

                      If you use new record/request to create the new records, you need to change layouts between steps 1 and 2 to set the context on the correct table.

                      1 of 1 people found this helpful