5 Replies Latest reply on Jul 30, 2014 8:47 AM by BrandonWoodward

    Checkbox to determine line items included in email body

    BrandonWoodward

      Title

      Checkbox to determine line items included in email body

      Post

           Hi,

           I'm fairly new so this might be an easy fix. I have a portal listing line items of product needed to order on the main dashboard. I've included a checkbox field called 'email' which is meant to indicate the line items to be included in the body of an email. The checkbox has a value of 1 when checked.

           The intention is to select multiple checkboxes and then execute an email script via button. Currently I am using the List ( myTable::description) function as a calculation in the body portion of the email to list products needed but I can't seem to figure out how to limit the list to only records where myTable::email = 1. 

            

           I appreciate any help in the right direction.

            

           Thanks

        • 1. Re: Checkbox to determine line items included in email body
          BrandonWoodward

               Below seems to be working but the script only evaluates the active row rather than every record in the portal

               AddRemoveListItem ( If ( myTable::email = 1; myTable::description; "") ;  "")

                

                

                

          • 2. Re: Checkbox to determine line items included in email body
            philmodjunk

                 AddRemvoveListItem would appear to be a custom function so we aren't likely to know what it does.

                 If you are using FileMaker 12 or newer, you can set up ExecuteSQL to produce this list by using a WHERE clause to check for the value 1 in this field.

                 Using older versions or if you don't want to try to use SQL, you can set up a relationship that only matches to line items with the specified invoice ID that also only have a 1 in the field and then List() will list the correct values via that relationship.

            • 3. Re: Checkbox to determine line items included in email body
              BrandonWoodward
                   Oh ya, forgot I added this a while ago.
                    
                   By: David Head
                    
                   Let ([
                   novalue = IsEmpty ( FilterValues ( theList ; value ) );
                   listminusvalue = Substitute( "¶¶" & theList & "¶¶"; [¶ & value & ¶ ; ¶ ] ; ["¶¶¶"; ""] ; ["¶¶"; ""] ) ;
                   listplusvalue = List ( theList; value )
                   ];
                   If ( novalue ; listplusvalue ; listminusvalue )
                   )
                    
                    
                   I'm trying to create the relationship as you suggested but how would I assign a value of one in the field?
                    
                   I've tried below but it doesn't seem to be working
                    
                   Set Field [myTableFilter::email;1]
                   Go to Related Record [show only related; from table: "myTableFilter"; Using: <current>]
                   Send Mail[Send via email client; subject: needed; List (myTable::description)
                   Exit Script []
              • 4. Re: Checkbox to determine line items included in email body
                philmodjunk

                     how would I assign a value of one in the field?

                     Define a calculation field with a number result type.

                     Enter this as your calculation:

                     1

                     Then include this field as an added match field in a relationship to your line items:

                     Invoice::__pkInvoiceID = LineItems|Selected::_fkInvoiceID AND
                     Invoice::constOne = LineItems|Selected::email

                     List ( LineItems|Selected::description )

                     would then produce a return separated list of Description values but only those where Email = 1.

                     LineItems|Selected is my name for a new Tutorial: What are Table Occurrences? of LineItems added so that you can keep your current relationship between the two tables unchanged.

                • 5. Re: Checkbox to determine line items included in email body
                  BrandonWoodward

                       Ok, got it. Thanks for the help!