After I pointed chatGTP to FileMaker's documentation I tried a few inquires.
Q: Using the While function, can convert the comma-separated list (previously supplied) into a JSON array
# Define the comma-separated list
Set Variable [ $list ; Value: "Company,Contact,Country¶Alfreds Futterkiste,Maria Anders,Germany¶Centro comercial Moctezuma,Francisco Chang,Mexico¶Ernst Handel,Roland Mendel,Austria¶Island Trading,Helen Bennett,UK¶Laughing Bacchus Winecellars,Yoshi Tannamuri,Canada¶Magazzini Alimentari Riuniti,Giovanni Rovelli,Italy" ]
# Convert the list to a JSON array
Set Variable [ $json ; Value: "[" ]
Set Variable [ $rowNumber ; Value: 1 ]
While [ $rowNumber ≤ ValueCount ( $list ) ]
Set Variable [ $row ; Value: Substitute ( GetValue ( $list ; $rowNumber ) ; "," ; "¶" ) ]
Set Variable [ $json ; Value: JSONSetElement ( $json ; "" ; $row ; JSONString ) ]
Set Variable [ $rowNumber ; Value: $rowNumber + 1 ]
If [ $rowNumber ≤ ValueCount ( $list ) ]
Set Variable [ $json ; Value: JSONSetElement ( $json ; "-" ; "," ; JSONObject ) ]
End If
End Loop
Set Variable [ $json ; Value: JSONSetElement ( $json ; "-" ; "]" ; JSONObject ) ]
# Display the JSON array
Show Custom Dialog [ "JSON Array" ; $json ]
.
———————————
interesting… I then tried a lighter task;
Q: Using the Substitution function, can you write a calculation to convert proper month names to abbreviated month names
- Yes, you can use the Substitute function in FileMaker to convert proper month names to abbreviated month names by creating a calculation field with the following formula:
Let ([ properMonth = "January"; abbreviatedMonth = "Jan"; properMonth2 = "February"; abbreviatedMonth2 = "Feb"; properMonth3 = "March"; abbreviatedMonth3 = "Mar"; properMonth4 = "April"; abbreviatedMonth4 = "Apr"; properMonth5 = "May"; abbreviatedMonth5 = "May"; properMonth6 = "June"; abbreviatedMonth6 = "Jun"; properMonth7 = "July"; abbreviatedMonth7 = "Jul"; properMonth8 = "August"; abbreviatedMonth8 = "Aug"; properMonth9 = "September"; abbreviatedMonth9 = "Sep"; properMonth10 = "October"; abbreviatedMonth10 = "Oct"; properMonth11 = "November"; abbreviatedMonth11 = "Nov"; properMonth12 = "December"; abbreviatedMonth12 = "Dec"; inputString = YourTable::properMonthField];
Substitute ( inputString ;
properMonth ; abbreviatedMonth ;
properMonth2 ; abbreviatedMonth2 ;
properMonth3 ; abbreviatedMonth3 ;
properMonth4 ; abbreviatedMonth4 ;
properMonth5 ; abbreviatedMonth5 ;
properMonth6 ; abbreviatedMonth6 ;
properMonth7 ; abbreviatedMonth7 ;
properMonth8 ; abbreviatedMonth8 ;
properMonth9 ; abbreviatedMonth9 ;
properMonth10 ; abbreviatedMonth10 ;
properMonth11 ; abbreviatedMonth11 ;
properMonth12 ; abbreviatedMonth12
))
Q: can you simplify that to just the Substitute function?
- Yes, you can use the Substitute function in FileMaker to convert proper month names to abbreviated month names by creating a calculation field with the following formula:
Substitute ( YourTable::properMonthField ;
"January" ; "Jan" ;
"February" ; "Feb" ;
"March" ; "Mar" ;
"April" ; "Apr" ;
"May" ; "May" ;
"June" ; "Jun" ;
"July" ; "Jul" ;
"August" ; "Aug" ;
"September" ; "Sep" ;
"October" ; "Oct" ;
"November" ; "Nov" ;
"December" ; "Dec"
)
Q: Can you add the understanding to the Substitute function; Multiple substitutions are allowed when you enclose each pair of searchString and replaceString parameters within brackets [ ]. Then try the calc again?
- Yes, you can use multiple substitutions with the Substitute function by enclosing each pair of searchString and replaceString parameters within brackets . Here's an updated version of the calculation using this syntax:
Substitute ( YourTable::properMonthField ;
["January"; "Jan"] ;
["February"; "Feb"] ;
["March"; "Mar"] ;
["April"; "Apr"] ;
["May"; "May"] ;
["June"; "Jun"] ;
["July"; "Jul"] ;
["August"; "Aug"] ;
["September"; "Sep"] ;
["October"; "Oct"] ;
["November"; "Nov"] ;
["December"; "Dec"]
)
cool