11 Replies Latest reply on Apr 10, 2017 2:24 PM by philmodjunk

    list as script parameter

    jdevans

      I'm trying to do a fix on a solution that I didn't create. I believe it was a modified FM Starter Solution, possibly from a recent version of Invoices.

      Anyway, there's a piece of it that uses List as a script parameter. It's List(field1;field2:field3...etc.) Inside the calling script, the Get(ScriptParameter) step assigns all those field values to a variable $selected.

      Then the fields in a separate table occurrence get set to the values found in $selected by using this construct:

      setNewField1(GetValue($selected; 1))

      setNewField2(GetValue($selected; 2))

      setNewField3(GetValue($selected; 3))...etc.

       

      The PROBLEM though is that the original data was imported from Excel, and one of the fields contains LineFeeds by way of Alt+Enter.

       

      During import, the second, third, etc line of data gets imported correctly, but when it gets "copied" via the get(scriptParameter) along with the List function, the second, third, etc lines of text get chopped off.

       

      I'm wondering if there's another approach. I was thinking that several Lookup Fields may suffice as a replacement. Since it's important not to tamper with the new records created by the way it's done now.

       

      Would another function do the  same thing?

        • 1. Re: list as script parameter
          philmodjunk

          Three options,

           

          1) when setting up the parameter list, use substitute to replace the returns with another character or string. Then when extracting parameters, use substitute again to put the returns back.

           

          2) use a different delimitter instead of returns. You can't use list or getvalue anymore, but you can use functions like position and middle to extract the text between | or some other character.

           

          3) Build a text expression as your parameter and use evaluate to evaluate the expression and assign values to variables.

           

          "Let ( [ $Parameter1 = " & Table::NumberField & "; $Parameter2 = " & Quote ( Table::TextField ) & " ] ; 1 ) "

           

          Might be your parameter expression and:

           

          Set Variable [ $EvaluationDummy ; value: Evaluate ( Get ( ScriptParameter ) ) ]

           

          Will assign 1 to $EvaluatationDummy and the value of Table::NumberField to $parameter1 and Table::TextField to $parameter2.

          1 of 1 people found this helpful
          • 2. Re: list as script parameter
            Jason Wood

            You could enhance what you have or you could start from scratch with a better solution.

             

            To fix what you have, you might use substitute to replace ¶ with something else you'd never see in your field like //cr//"

             

            Assuming field3 is your field that might have a carriage return:

             

            List(field1;field2;Substitute(field3;"¶";"//cr//")...)

             

            Then just remember to substitute back when you're pulling the value.

             

            Substitute(GetValue($selected; 1);"//cr//";"")

             

            OR

             

            You could forget about this whole return delimited list strategy and try something else. I recommend downloading the example file from http://www.filemakerstandards.org and get to know the "Let Notation" functions (the ones that start with #). These are custom functions so you'll need FileMaker Advanced to add them to your file.

            1 of 1 people found this helpful
            • 3. Re: list as script parameter
              jdevans

              On your first suggestion, I've already tried that, but not sure if I'm "searching" for the correct character.

              My research tells me that Alt+Enter in Excel is essentially Unicode 10. So, I tried using Substitute inside the List function for the field that is likely to have the line feeds. I tried Substitute(field1;Char(10);Pilcro), but it didn't seem to do anything. However, I didn't try to put them back as you suggested at the extraction point.

               

              Can you use Char function as I attempted?

              • 4. Re: list as script parameter
                jdevans

                My first inclination was to do something different completely, but then I thought it might be as simple as modifying the Script Parameter. Apparently, it isn't as simple as that.

                • 5. Re: list as script parameter
                  philmodjunk

                  char(10) = ¶

                   

                  So why would you substitute the same text for the same text?

                   

                  What I have sometimes used is this:

                   

                  Parameter expression:

                   

                  List ( Substitute ( Table::FieldWithMultiLines ; ¶ ; "§" ) ; Table::anotherfield )

                   

                  And then:

                   

                  Substitute ( GetValue ( Get ( ScriptParameter ) ; 1 ) ; "§" ; ¶ )

                   

                   

                  1 of 1 people found this helpful
                  • 6. Re: list as script parameter
                    jdevans

                    I didn't think I was. I thought that Char(13) was a carriage return.

                     

                    Is there a way to analyze what's actually there, character wise, so that I know what to search for in the substitute?

                    • 7. Re: list as script parameter
                      philmodjunk

                      I think that GetValue won't make a distinction between Char ( 10 ) and Char (13) but I could be wrong.

                       

                      You shouldn't need to do that kind of analysis. Just use ¶ when substituting out the returns and I'd think that would work here.

                       

                      But if you do want to check the character codes of your data one character at a time, you can use the code( ) function.

                      1 of 1 people found this helpful
                      • 8. Re: list as script parameter
                        annr

                        Will this be a batch transfer or automated? While you can probably find a FileMaker solution, Excel is really handy for this type of data cleaning. I end up with a checklist of the weird things that source may insert, and do a quick series of search/replace. Will also let someone catch the blanks if that's an issue on your design (List will shift everything up a slot when there's an empty field).

                        • 9. Re: list as script parameter
                          jdevans

                          philmodjunk I was getting too wrapped up in which character was being imported when Alt+Enter is in the Excel data. I tried your suggestion of Substitute at the List function, and doing the opposite Substitute at the GetValue function. Works great.

                           

                          THANKS!!

                          • 10. Re: list as script parameter
                            jdevans

                            annr    probably batch. The main user of this data says he's creating a shared Excel file for periodic imports into the Filemaker Solution. He stressed that any carriage returns would need to remain for readability.

                            • 11. Re: list as script parameter
                              philmodjunk

                              and to correct a detail, ¶ has a code of 13 as you stated.

                               

                              But Getvalue will not distinguish between data separated by chart ( 10 ) from data separated by char ( 13 ). Both will be treated as value separators or so a quick test in the data viewer seems to indicate to me.

                              1 of 1 people found this helpful