9 Replies Latest reply on Aug 20, 2012 5:24 PM by Kirk_1

    Let Statement

    Kirk_1

      Title

      Let Statement

      Post

      How do i do a Let statement that segragates the states for me?

      Ex... ~W = the 11 westeren states of AZ, CA, CO, ID, MT, NV, NM, OR, UT, WA and WY?

      I tried doing exactly this way but its looking for a filemaker field.

      Do i have to make a filemaker varable for each State?

       

       

        • 1. Re: Let Statement
          philmodjunk

          Can't really say.

          What exactly are you trying to do? What do you mean by "segregate" and what are your segregating? Records in a table? In a portal? Values combined in a single field?

          • 2. Re: Let Statement
            Kirk_1

            The US will be broken up in to 4 regions.

            ~W region = AZ, CA....

            ~MW region = IL, IN, IA...

            I need a clean way to have ~W = all of the w states, ~MW = all of those...

            Then ill do a case statement, ~W = Quote::QTshipperstate; Discounts::DcustperW_W

            • 3. Re: Let Statement
              Kirk_1

              Im doing it with in the script pulling the info from tables.

              • 4. Re: Let Statement
                philmodjunk

                Which means that you need a field for the State abbreviations.

                Add a calculation field with this expression: (Don't really need the Let function)

                Case ( PatternCount ( "AZ CA" ; YourTable::State ) ; "~w" ;
                          PatternCount ( "IL IN IA" ; YourTable::State ) ; "~mw" ;
                          // and so forth and including all desired state abbreviations in the above lists as well...
                         )

                And you won't need the case function you are describing if you define a relationship that matches records using the above calculation field and the Quote::QTshipperstate field. You don't even need a script, a looked up value or auto-enter calculation can copy the current quote for that region into a field in your main table via this relationship. (By copying the value this way, a rate change will only affect new records and past records will not update to show a rate that was not current at the time the record was created.)

                • 5. Re: Let Statement
                  Kirk_1

                  Ill try it.

                   

                  Thank you

                  • 6. Re: Let Statement
                    Kirk_1

                    Im doing the calculated field. I created fields in the rate calculation table called shipstate and constate.

                    Below is the calculation im putting in the Rate Calculation::shipstate field. It won't accept the ("IL IN IA KY..." ; i get the error message "the specificed field can not be found.

                    Its looking for a filemake field... I thought thats what im trying to do.

                    If the shipstate is AZ it will assign ~ W to that. I then to the "constate", get the value for that. Lets say thats IL so that will get the value ~MW...

                    I compare this with my discounts table, find the amount that represents W to MW, pull that number and * it...

                    Case (
                                    Patterncount ("AZ CA CO ID MT NV NM OR UT WA WY"; Rate Calculation::Shipstate); "~W';
                                    Patterncount ("IL IN IA KY MI OH WV WI" ; Rate Calculation::Shipstate) ; "~MW";
                    )

                    • 7. Re: Let Statement
                      philmodjunk

                      You have a single quote after ~W where you need a double quote, judging by what you have just posted.

                      You have:

                      "~W'

                      You should have:

                      "~W"

                      • 8. Re: Let Statement
                        Kirk_1

                        That was it,

                         

                        Thank you

                        • 9. Re: Let Statement
                          Kirk_1

                          Thats working well.

                          Great Idea.

                          Thank you