Skip to main content

FileMaker Tips and Tricks

using-executesql-to-find-like

ExecuteSQL ( "SELECT "cc.email" FROM cc WHERE "cc.CustomerNumberKey" = 1000 AND "cc.Subscriptions" LIKE '%Critical Needs%' " ; "" ; "" )

Use "Like" then the value you wish to find with "%" on the at the beginning and end. This will look for that phrase anywhere in the field.

CF for Returning ExecuteSQL results as JSON

Great blog on how to use a ExecuteSQL statement to return results in JSON format.

https://the.fmsoup.org/t/cf-for-returning-executesql-results-as-json/2403

S3 as a FileMaker Container Alternative

Get information on integration S3 buckets into FileMaker.

https://definedatabase.com/s3-object-storage-filemaker-container-alternative/

Using IN Statement in ExecuteSQL - FileMaker

Let

(

[
idArray = (can use a variable $idarray or field name);

idSQL = "SELECT FirstName FROM users WHERE id IN (" & idArray & ") "
];

ExecuteSQL ( idSQL ; "" ; "" )

)

The whole SQL statement needs to be inserted using a variable for the "IN ()" array to work.

Creating a ExecuteSQL Using "LIKE" in the Query

Example:

ExecuteSQL ( "SELECT nameFirst, nameLast FROM pl_people WHERE LOWER(nameLast) LIKE '%' || ? || '%'" ; " " ; "" ; "jones" )

  • 1 comment

Create a JSON Array from a ExecuteSQL Query

# Generate JSON Data for Datawarehouse

Set Field [ im::JSONLabelItem ; JSONSetElement ( "" ; ["Plant" ; 25 ; JSONNumber]; ["ItemRef" ; im::i.a.ItemReference ; JSONString]; ["CustomerNum" ; im::i.c.b.CustNum ; JSONNumber]; ["CustomerName" ; im::i.c.b.CustName ; JSONString]; ["Description" ; im::i.a.description ; JSONString]; ["Serial" ; im::i.a.ItemSerialNum ; JSONNumber]; ["File" ; im::i.a.ItemFileNum ; JSONNumber]; ["Method" ; im::i.a.Type_q.ProductionMethod ; JSONString]; ["Die" ; im::q.l.TOOL ; JSONString]; ["Size" ; im::q.l.Tool_Width & " x " & im::q.l.Tool_Height ; JSONString]; ["Facestock" ; im::q.l.FS.REFERENCE ; JSONString]; ["Material" ; im::q.l.FS_Descrip ; JSONString]; ["Laminate" ; im::q.l.LA_Descrip ; JSONString]; ["Varnish" ; im::q.l.VARNISH ; JSONString] ) ]

Set Variable [ $ItemJSON ; Value: JSONSetElement ( "" ; ["Plant" ; 25 ; JSONNumber]; ["ItemRef" ; im::i.a.ItemReference ; JSONString]; ["CustomerNum" ; im::i.c.b.CustNum ; JSONNumber]; ["CustomerName" ; im::i.c.b.CustName ; JSONString]; ["Description" ; im::i.a.description ; JSONString… ]

Set Variable [ $WebPricingInfo ; Value: ExecuteSQL ( "SELECT ItemID,Qty,Price,UOM,PriceOverride,WebPrice FROM WebPricing WHERE ItemID = ?" ; "" ; "" ; im::i.a.ItemSerialNum ) ]

Set Variable [ $count ; Value: GetAsNumber ( Substitute ( ValueCount ( $WebPricingInfo ) ; "," ; ¶ ) ) ]

Set Variable [ $cnt ; Value: 1 ]

# Create Pricing List JSON

If [ $count>0 ]

Loop [ Flush: Always ]

Set Variable [ $row ; Value: Substitute ( GetValue ( $WebPricingInfo ; $cnt ) ; "," ; ¶ ) ]

Set Variable [ $rowFormat ; Value: JSONSetElement ( "" ; ["ItemID" ; GetValue ( $row ; 1 ) ; JSONString]; ["Qty" ; GetValue ( $row ; 2 ) ; JSONNumber]; ["Price" ; GetValue ( $row ; 3 ) ; JSONNumber]; ["UOM" ; GetValue ( $row ; 4 ) ; JSONString]; ["PriceOverride" ; GetValue ( $row ; 5 ) … ]

Set Variable [ $ItemWebPricingJSON ; Value: $ItemWebPricingJSON & $rowFormat & If ( $cnt = $count ; ""; "," ) ]

Set Variable [ $cnt ; Value: $cnt + 1 ]

Exit Loop If [ $cnt > $count ]

End Loop

End If

// Set Variable [ $ItemWebPricingJSON ]

# 

# 

# Update Data Warehouse (FlexlinkAllDW)

Set Variable [ $serial ; Value: im::i.a.ItemSerialNum ]

Set Variable [ $plant ; Value: JSONGetElement ( $ItemJSON ; "Plant" ) ]

Set Variable [ $json ; Value: im::JSONLabelItem ]

Freeze Window

New Window [ Style: Card ; Name: "Update Item" ; Using layout: \ ; Height: 50 ; Width: 50 ]

Go to Layout [ “JSONDWItems” (JSONDWItems) ; Animation: None ]

Set Error Capture [ On ]

Perform Find [ Restore ]

If [ Get(FoundCount)=0 ]

New Record/Request

Set Field [ JSONDWItems::JSONLabelItem ; $json ]

Else

Set Field [ JSONDWItems::JSONLabelItem ; $json ]

End If

Close Window [ Current Window ]

# 

// Set Variable [ $post_data ; Value: "{\"token\":\"xxxxx\",\"data\":["& $ItemJSON & "]}" ]

// Insert from URL [ Select ; With dialog: On ; Target: $ResultField ; "https://api.integrator.io/v1/exports/xxxxx/data" ; cURL options: " --request POST" & " --header \"Content-Type: application/json\"" & " -d @$post_data" ]

// Show Custom Dialog [ "Result" ; $ResultField ]

Using FileMaker Send JSON to an API (FM Webhooks)

Set Variable [ $post_data ; Value: Items::JSONLabelItem ]

Insert from URL [ Select ; With dialog: Off ; Target: $ResultField ; "https://shipleymedia.com/\\" ; cURL options: " --request POST" & " --header \"Content-Type: application/json\"" & " -d @$post_data" ]

Show Custom Dialog [ "Result" ; $ResultField ]

Putting a invoice in to JSON (sample)

# Generate JSON Data for Datawarehouse
# Create Invoice Part of JSON

Set Variable [ $InvoiceJSON ; Value: JSONSetElement ( "" ; ["Plant" ; 25 ; JSONNumber]; ["AMNum" ; am::a.a.AMnum ; JSONNumber]; ["InvoiceNumber" ; am::a.am.INVRef ; JSONNumber]; ["CustomerNumber" ; am::a.c.b.CustNum ; JSONNumber]; ["CustomerName" ; am::a.c.b.CustName ; JSONString]; ["Type… ]

Set Variable [ $invNum ; Value: am::a.a.AMnum ]

Set Variable [ $lineItems ; Value: ExecuteSQL ( "SELECT \"ad.Description\",\"ad.QTY\",\"ad.Amount\",\"z.c.LineSubTotal\",\"z.c.LineTax\",\"ad.Category\",\"ad.Class\",\"ad.ClassFamily\",\"ad.ClassFamilyDivision\" FROM \"am.ad\" WHERE \"ad.AMnum\" = ?" ; "" ; "" ; am::a.a.AMnum ) ]

Set Variable [ $count ; Value: Substitute ( ValueCount ( $lineItems ) ; "," ; ¶ ) ]

Set Variable [ $cnt ; Value: 1 ]

# Create Invoice Items Part of JSON

Loop [ Flush: Always ]

Set Variable [ $row ; Value: Substitute ( GetValue ( $lineItems ; $cnt ) ; "," ; ¶ ) ]

Set Variable [ $rowFormat ; Value: JSONSetElement ( "" ; ["ItemDecription" ; GetValue ( $row ; 1 ) ; JSONString]; ["QTY" ; GetValue ( $row ; 2 ) ; JSONNumber]; ["Price" ; GetValue ( $row ; 3 ) ; JSONNumber]; ["LineTotal" ; GetValue ( $row ; 4 ) ; JSONNumber]; ["SalesTax" ; GetValue ( $r… ]

Set Variable [ $JSONLineItems ; Value: $JSONLineItems & $rowFormat & If ( $cnt = $count ; ""; "," ) ]

Set Variable [ $cnt ; Value: $cnt + 1 ]

Exit Loop If [ $cnt > $count ]

End Loop

# 

Freeze Window

# Add JSON to Datawarehouse

New Window [ Style: Card ; Name: "Update Item" ; Using layout: “DWInvoice” (DWInvoice) ; Height: 50 ; Width: 50 ]

Set Error Capture [ On ]

Perform Find [ Restore ]

If [ Get(FoundCount)=0 ]

New Record/Request

Set Field [ DWInvoice::DatabaseID ; $invNum ]

Set Field [ DWInvoice::JSONInvoice ; "{ \"Invoice\": " & $InvoiceJSON & ", \"Items\": [" & $JSONLineItems & "]}" / Replace ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ; Length ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ) - 2 ; 1 ; "" ) / ]

Commit Records/Requests [ With dialog: Off ]

Else

Set Field [ DWInvoice::DatabaseID ; $invNum ]

Set Field [ DWInvoice::JSONInvoice ; "{ \"Invoice\": " & $InvoiceJSON & ", \"Items\": [" & $JSONLineItems & "]}" / Replace ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ; Length ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ) - 2 ; 1 ; "" ) / ]

Commit Records/Requests [ With dialog: Off ]

End If

Close Window [ Current Window ]

Server Side Emails with Attachment from Container Field

If [ not IsEmpty ( EmailLog::Attachment ) ]
# Define temporary path and file variables
Set Variable [ $_tempPath ; Value: Get(TemporaryPath) ]
Set Variable [ $_fileName ; Value: GetContainerAttribute ( EmailLog::Attachment ; "filename" ) ]
Set Variable [ $_filePath ; Value: $_tempPath & $_fileName ]

# Check if the file already exists
Get File Exists [ “$_filePath” ; Target: $fileExists ]

If [ $fileExists = 1 ]
    Delete File [ Target: “$_filePath” ]
End If

# Create and write to the data file
Create Data File [ “$_filePath” ; Create folders: On ]
Open Data File [ “$_filePath” ; Target: $_filePathID ]

# Writing data to the file
Write to Data File [ File ID: $_filePathID ; Data source: EmailLog::Attachment ; Write as: Base64 ]
Close Data File [ File ID: $_filePathID ]

# Prepare the email parameters
Set Variable [ $emailTo; Value: "recipient@example.com" ]
Set Variable [ $emailSubject; Value: "Email Subject" ]
Set Variable [ $emailBody; Value: "This email includes an attachment." ]

# Send the email
Send Mail [
    Send via SMTP Server: "smtp.example.com" ;
    From: "your-email@example.com" ;
    To: $emailTo ;
    Subject: $emailSubject ;
    Message: $emailBody ;
    Attachment: $_filePath ;
    SMTP Authentication: On ;
    User name: "your-username" ;
    Password: "your-password"
]

# Clean up by deleting the temporary file
Delete File [ Target: “$_filePath” ]

End If

Multi Join ExecuteSQL Statement

Trip::Countries (Safari database)

/ This show days in destination /

ExecuteSQL ( "SELECT DISTINCT(a.Name), SUM(ii.x_Days)

FROM Area AS a

INNER JOIN  Destination d

ON d.\"_kf_area\" = a.\"__kp_Area\"

INNER JOIN  Accommodation accom

ON accom.\"_kf_Destination\" = d.\"__kp_Destination\"

INNER JOIN  ItineraryItem ii

ON ii.\"_kf_Accommodation\" = accom.\"__kp_Accommodation\"

INNER JOIN  Trip t

ON t.\"__kp_Trip\" = ii.\"_kf_trip\"

WHERE t.\"__kp_Trip\" = ? GROUP BY a.Name";"" ;" / " ; Trip::__kp_Trip )

/ This show minus days in destination /

ExecuteSQL ( "SELECT DISTINCT(a.Name)

FROM Area AS a

INNER JOIN  Destination d

ON d.\"_kf_area\" = a.\"__kp_Area\"

INNER JOIN  Accommodation accom

ON accom.\"_kf_Destination\" = d.\"__kp_Destination\"

INNER JOIN  ItineraryItem ii

ON ii.\"_kf_Accommodation\" = accom.\"__kp_Accommodation\"

INNER JOIN  Trip t

ON t.\"__kp_Trip\" = ii.\"_kf_trip\"

WHERE t.\"__kp_Trip\" = ? GROUP BY a.Name";":" ;" / " ; Trip::__kp_Trip )