fmgenius

Filemaker natively supports ftps but not sftp. These are 2 different protocols. sftp uses SSH for the connection while ftps uses SSL connection.

Fantastic! Thank you, Geoffrey. I understand about the VOID but my client is--unconventional.

In order to modify a transaction, you have to provide the transaction ids and edit sequence. If my modification has more expense lines than the original, the documentation says that I just have to pass "-1" as the transaction line to add a new line. What if the QB version of the transaction originally had more expense lines than your modification. Is it possible (and how to) to delete the extra expense lines in a mod request?

Problem solved. Use \" rather than '

I am trying to do a basic Insert from URL call with some CURL headers added and I keep getting authentication errors. This is the curl command sent by the provider for me to use. curl -X GET --header 'Accept: application/json' --header 'api_key: 1234567890abcdefg' 'https://mydomain.leaddocket.com/api/endpoint' This is the curl I am setting up -X GET \ --header 'Accept: application/json' \ --header 'api_key: 1234567890abcdefg' \ -trace $$curlTraceDump FM barks and says "Authentication Failed" but if I do this with the BaseElements plugin, it works just fine. What I found when looking at the Curl Trace was that FM is dropping some of the headers I send and injecting its own. Here's the snippet I am finding from FM's trace: GET /api/Leads/P endingExportIds HTTP/1.1 Host: mydomain.leaddocket.com User-Agent: FileMaker/16.0 Accept: */* Accept-Encoding: deflate, gzip Notice: both headers I sent were stripped and replaced. Here is the response from BaseElements trace GET /api/endpoint HTTP/1.1 Host: mydomain.leaddocket.com User-Agent: libcurl-agent-base-elements-plugin/3.3.4 Accept: application/json api_key: 1234567890abcdefg BE left my headers intact. So what gives?

I just spoke with Sarah from 360Works and while 360Works does have some issues to work out, she assured me that functionally MirrorSync will continue to function as expected even in iOS 7.

In light of this announcement: http://help.filemaker.com/app/answers/detail/a_id/12074 How does this affect MirrorSync and other sync technologies?

This looks simple and correct to me yet in this case, FM will not return the null value for a value A6 on the left. Here is the example SQL SELECT "sach"."AchievementNumber", COUNT("searned"."_scout") FROM "sach" LEFT OUTER JOIN "searned" ON "sach"."_id" = "searned"."_achievement" WHERE "searned"."_scout" = ? and "isRegularAchivement" = 1 GROUP BY "sach"."AchievementNumber" ORDER BY "sach"."AchievementNumber" I should get 12 rows every time, yet this is all I get: A1,6 A2,8 A3,3 A4,7 A5,10 A7,4 A8,2 A9,5 A10,3 A11,4 A12,5 if I run this SELECT DISTINCT( ''||"AchievementNumber" ), "requiredAchievements" FROM "sach" WHERE "isRegularAchivement" = ? ORDER BY "AchievementNumber" A1,6 A2,7 A3,3 A4,6 A5,5 A6,3 A7,6 A8,5 A9,5 A10,3 A11,4 A12,5 Why won't FM properly return a null for the missing rows for A6?

Good call, Wim, but that will only delay the inevitable. Once I get the listing it's easy to confirm that the file being worked on exists (not as clean as the simple exists() )...but, I still need the SM functions to work when I need to zip and move that file into a Archive folder. @Claus - yes, see in my post. I have already confirmed that the plugin is registered and functioning. When you say limited access--please clarify. I know that I only have access to the Documents folder. Is it limited further than that? Am I unable to create new folders, move/rename files, etc in that Documents folder?

Hey Wim, These files are basic tab delimited data being retrieved from another system and placed in the Server Documents folder. In my solution I am keeping a list of these target files in a table to make it end user accessible if they need to add more files to the import process later on. The start of the script gets the list of these files intended to be imported and in a loop runs this import process. The first step in that process is to ask, "Does this file exist?" I am using the script that comes in the SM file, I just have it renamed as smFileExists, the groovy is new File( pathToFile ).exists() I pass in as a parameter Substitute ( Get ( DocumentsPath ) ; Get ( SystemDrive ) ; "/" ) & "myfile.tab" to strip the initial "/Mac HD/" and then concatenating the name of the file. Run from the client, it works--for example, I place the tab file in my user documents folder. Initially, my concern was the the plugin was not loading or working with server. So to check that, I am using FM send mail via smtp and I run the function SMGetRegisteredModules to get a list of all functions registered at startup and I get back my full list. So I know that the plugin is functioning. I don't know if seeing the code will help. I will post those separately. This is the script the server calls. # #============================================== # Purpose: Import Controller to update inventory. Performed nightly # Parameters: none # --------------------------- # --------------------------- # Called by: (script) any # Author: Rob Poelking # History: 2012-11-08 Rob Poelking Intial Version # 2013-01-10 Rob Poelking - # Notes: Need more error trapping functions - failure of plugin - failure to import file - failure to find file #============================================== #Variables Set Variable [$arc; Value:"archive/"] Set Variable [$arc.limit; Value:5] #============================================== # # #============================================== #DEBUG - REMOVE BEFORE DEPLOYMENT # Set Variable [$$DEBUG; Value:True] Set Variable [$mail.vars; Value://Assign All vars Let ([ $from.name = "server" ; $from.address = "server@server.local" ; $smtp.server = "smtp.gmail.com" ; $smtp.port = 465 ; $mail.user = "me.com" ; $mail.pw = "password" ; $to = $mail.user ]; True )] Set Variable [$subject; Value:"Inventory Import Status"] #============================================== # # # # Set Error Capture [On] Allow User Abort [Off] # # #need to load the NADA modules! Set Variable [$check.modules; Value:NADA_CheckModules] If [$check.modules = 0] Perform Script ["Load Scripts"] End If #If they haven't loaded by now, stop If [$check.modules = 0] #send mail Set Variable [$message; Value:"Failure; check modules won't load"] Send Mail [No dialog; Send via SMTP Server; To: "$to"; Subject: "$subject"] Exit Script [] Else #============================================== DEBUG If [$$DEBUG] Set Variable [$message; Value:List ( "Success; check modules loaded" ; SMGetRegisteredModules )] Send Mail [No dialog; Send via SMTP Server; To: "$to"; Subject: "$subject"] End If #============================================== DEBUG End If # Go to Layout ["Inventory Import"] #Query the importFiles table for a list of files to be imported # # Set Variable [$file.list; Value:Let ([ sql = "SELECT [~fieldlist~] FROM [~table~] WHERE [~queryfield~] = ?"; fieldlist = Substitute( List( sql.GetFieldNameFromID ( 1065121 ; 5 ) ; //filename sql.GetFieldNameFromID ( 1065121 ; 4 ) ; //storecode sql.GetFieldNameFromID ( 1065121 ; 7 ) ; //type ); ¶ ; ", " ) ; table = sql.GetTableNameFromID ( 1065121 ) ; //"@importFiles" queryField = sql.GetFieldNameFromID ( 1065121 ; 2 ); queryValue = "Inventory" ; sql = Substitute( sql; [ "[~fieldlist~]" ; fieldlist ] ; [ "[~table~]" ; table ] ; [ "[~queryfield~]" ; queryfield ] ) ; return = ExecuteSQL ( sql ; "" ; "" ; queryValue ) ]; return )] Set Variable [$file.count; Value:ValueCount ( $file.list )] #============================================== DEBUG If [$$DEBUG] Set Variable [$message; Value:List ( $file.count & "File Listed:" ; $file.list )] Send Mail [No dialog; Send via SMTP Server; To: "$to"; Subject: "$subject"] End If #============================================== DEBUG If [$file.count] Loop Exit Loop If [Let ( $i=$i+1;$i>$file.count)] #break out file data Set Variable [$file.data; Value:Substitute ( GetValue ( $file.list ; $i ) ; "," ; ¶ )] Set Variable [$file.name; Value:GetValue ( $file.data ; 1 )] Set Variable [$store.code; Value:GetValue ( $file.data ; 2 )] Set Variable [$type; Value:GetValue ( $file.data ; 3 )] # # #Make sure file exists Set Variable [$path; Value:Get ( DocumentsPath )] Perform Script ["File Management [ action {; patha ; pathb ; data } ]"; Parameter: List ( # ( "action" ; "exists" ) ; # ( "patha" ; $path & $file.name) )] Set Variable [$result; Value:Get ( ScriptResult )] # # #============================================== DEBUG If [$$DEBUG] Set Variable [$message; Value:List ( $file.data ; $file.name ; $store.code ; $type ; $path & $file.name; "file exists? " & Choose ( $result ; "No" ; "Yes" ) )] Send Mail [No dialog; Send via SMTP Server; To: "$to"; Subject: "$subject"] End If #============================================== DEBUG # # If [$result // file exists] #get the parameters needed to perform the import Perform Script ["Import Inventory [ filename ; storecode; type ]"; Parameter: List ( # ( "filename" ; $file.name ) ; # ( "storecode" ; $store.code ) ; # ( "type" ; $type ) )] # // Set Variable [$path; Value:Get ( DocumentsPath ) & $file.name] #Make sure we have an archive folder at destination Perform Script ["File Management [ action {; patha ; pathb ; data } ]"; Parameter: List ( # ( "action" ; "exists" ) ; # ( "patha" ; $path & $arc ) )] Set Variable [$result; Value:Get ( ScriptResult )] If [not $result] #folder doesn't exist, create it now Perform Script ["File Management [ action {; patha ; pathb ; data } ]"; Parameter: List ( # ( "action" ; "create folder" ) ; # ( "patha" ; $path & $arc ) )] End If #Make sure we did not get a failure code Set Variable [$result; Value:Get ( ScriptResult )] If [not $result] #@TODO 2013-01-17 Rob Poelking - not sure how to handle failure Exit Script [] End If #check to see if we have reached the limit on archive files and delete oldest if necessary Perform Script ["File Management [ action {; patha ; pathb ; data } ]"; Parameter: List ( # ( "action" ; "list folder" ) ; # ( "patha" ; $path & $arc ) )] Set Variable [$result; Value://if mac, strip the volume name Let ( [ basePath = If ( isMac ; Substitute ( $path ; Get ( SystemDrive ) ; "/" ) ; $path ) & $arc ]; Substitute ( Get ( ScriptResult ) ; basePath ; "" ) )] Set Variable [$file.search; Value:Substitute ( $file.name ; ".tsv" ; "" )] Set Variable [$file.count [2]; Value:PatternCount ( $result ; $file.search )] If [$file.count[2] ≥ $arc.limit] Set Variable [$files.delete.count; Value://Returning only the list of files that match the current file. Eliminate all but the most current list //that are within the archive limit ($arc.limit) // 2013-01-17 Rob Poelking Let ( [ r = $result ; r = If ( Right ( r ; 1 ) ≠ ¶ ; r & ¶ ; r ) ; s = $file.search ; c = PatternCount ( r ; s ); p1 = Position ( r ; s ; 1 ; 1 ); pLast = Position ( r ; s ; 1 ; c ) ; startingValue = PatternCount ( Left ( r ; p1 -1 ) ; ¶ ) +1 ; $list = MiddleValues ( r ; startingValue ; c ) ]; ( ValueCount ( $list ) + 1 ) - $arc.limit )] Loop Exit Loop If [Let ( $j=$j+1;$j>$files.delete.count )] Set Variable [$target.file; Value:GetValue ( $list ; $j )] Perform Script ["File Management [ action {; patha ; pathb ; data } ]"; Parameter: List ( # ( "action" ; "delete file" ) ; # ( "patha" ; $path & $arc & $target.file ) )] End Loop End If #compress and move file to archive Perform Script ["File Management [ action {; patha ; pathb ; data } ]"; Parameter: Let ( [ now = Get ( CurrentHostTimeStamp ) ; y = Year ( now ) ; m = Right ( "00" & Month ( now ) ;2 ) ; d = Right ( "00" & Day ( now ) ;2 ) ; H = Right ( "00" & Hour ( now ) ;2 ) ; n = Right ( "00" & Minute ( now ) ;2 ) ; s = Right ( "00" & Seconds ( now ) ;2 ) ; ts = y & m & d & h & n & s ]; List ( # ( "action" ; "zip file" ) ; # ( "patha" ; $path & $file.name ) ; # ( "pathb" ; $path & $arc & Substitute ( $file.name ; ".tsv" ; "_" & ts & ".zip" ) ) ) )] Perform Script ["File Management [ action {; patha ; pathb ; data } ]"; Parameter: List ( # ( "action" ; "delete file" ) ; # ( "patha" ; $path & $file.name ) ; )] # #//file does not exist End If # End Loop # #===================================================================== #In the event no file were found, then we would have a zero found count If [Get ( FoundCount )] #Eliminate any temp inventory that already exists in inventory Set Variable [$stock.numbers; Value://Returns all matching records that exist already in inventory //do not import these Let( [ sql = "SELECT [~field~] FROM [~table~] INNER JOIN [~table2~] ON ( [~j1~] = [~j2~] ) " ; // WHERE [~queryField~] = ?"; field = sql.GetFieldNameFromID ( 1065118 ; 1 ) ; table = sql.GetTableNameFromID ( 1065118 ) ;//tmpInventory table2 = sql.GetTableNameFromID ( 1065103 ) ; //inventory joinField1 = sql.GetFieldNameFromID ( 1065118 ; 1 ) ;//stocknumber joinField2 = sql.GetFieldNameFromID ( 1065103 ; 11 ) ;//stocknumber queryField = joinField2 ; queryValue = Quote ( "" ) ; sql = Substitute( sql; [ "[~field~]" ; field ] ; [ "[~table~]" ; table ] ; [ "[~table2~]" ; table2 ] ; [ "[~j1~]" ; joinField1 ] ; [ "[~j2~]" ; joinField2 ] ; [ "[~queryField~]" ; queryField ] ) ; return = ExecuteSQL ( sql ; "" ; "" ; queryValue ) ]; return )] If [ValueCount ( $stock.numbers )] Set Field [@tmpInventory::stockMatch[]; $stock.numbers] Go to Related Record [Show only related records; From table: "temInventory stock match 2"; Using layout: <Current Layout>] If [Get ( LastError ) = 0] Delete All Records [No dialog] End If End If # #Determine what stock has been sold #@TODO - eliminate marking same records repeatedly. Eliminate anything already marked sold. Set Variable [$stock.numbers; Value://Returns all records that exist in inventory but no longer in the import //mark all these inventory records as sold Let( [ sql = "SELECT [~field~] FROM [~table~] LEFT JOIN [~table2~] ON [~j1~] = [~j2~] WHERE [~queryField~] IS NULL AND "Sold" IS NULL"; field = sql.GetFieldNameFromID ( 1065103 ; 11 ) ; table2 = sql.GetTableNameFromID ( 1065118 ) ;//tmpInventory table = sql.GetTableNameFromID ( 1065103 ) ; //inventory joinField2 = sql.GetFieldNameFromID ( 1065118 ; 1 ) ;//stocknumber joinField1 = sql.GetFieldNameFromID ( 1065103 ; 11 ) ;//stocknumber queryField = joinField2 ; sql = Substitute( sql; [ "[~field~]" ; field ] ; [ "[~table~]" ; table ] ; [ "[~table2~]" ; table2 ] ; [ "[~j1~]" ; joinField1 ] ; [ "[~j2~]" ; joinField2 ] ; [ "[~queryField~]" ; queryField ] ) ; return = ExecuteSQL ( sql ; "" ; "" ) ]; return )] If [ValueCount ( $stock.numbers )] Set Field [@tmpInventory::stockMatch[]; $stock.numbers] Go to Related Record [Show only related records; From table: "inventory » importStockMatch"; Using layout: "@inventory"] If [Get ( LastError ) = 0] Replace Field Contents [No dialog; @inventory::Sold[]; True] End If Go to Layout ["Inventory Import"] End If # #Finally, any remaining inventory needs to be cleaned and imported #At this point, clean up the import, normalizing it to NADA auto data. Perform Script ["Clean Imported Inventory"] #check for error #fill in gap information for series/trims/accessories #get missing series (models) Perform Find [Restore] If [Get ( FoundCount)] Go to Record/Request/Page [First] Loop Perform Script ["Get Current Series"] Go to Record/Request/Page [Next; Exit after last] End Loop End If # #get missing trim Perform Find [Restore] If [Get ( FoundCount)] Go to Record/Request/Page [First] Loop Perform Script ["Get Current Trims"] Go to Record/Request/Page [Next; Exit after last] End Loop End If # #get missing accessories Perform Find [Restore] If [Get ( FoundCount)] Go to Record/Request/Page [First] Loop Perform Script ["Get Current Accessories"] Go to Record/Request/Page [Next; Exit after last] End Loop End If # # #======================================================== #@TODO : need to import from the temp table into the regular inventory table #In the context of the temp Inventory, show all records so they get imported Go to Layout ["Inventory Import"] Show All Records # #Go to regular inventory table Go to Layout ["@inventory"] #now do a self import from the tempInventory to the Inventory table Import Records [No dialog; "mst_data"; Add; Mac Roman] # #Now empty the tempInventory table Go to Layout ["Inventory Import"] Delete All Records [No dialog] # #- end if [ Get ( FoundCount ) ) ] End If End If Go to Layout [original layout] # Exit Script [] This is the script that manages the file operations. # #============================================== # Purpose: Controls server side file management # Parameters: action ; path # --------------------------- # $action = (enum) # $path = (string) any valid path string # --------------------------- # Called by: (script) any # Author: Rob Poelking # History: 2013-01-17 Rob Poelking - initial version # Notes: none #============================================== #VARIABLES #============================================== # Set Variable [$vars; Value:#AssignScriptParameters] #Need to truncate mac volume not sure if this is the case on windows If [isMac] Set Variable [$patha; Value:Substitute ( $patha ; Get ( SystemDrive ) ; "/" )] Set Variable [$pathb; Value:Substitute ( $pathb ; Get ( SystemDrive ) ; "/" )] End If # # If [$action = ""] #-------------------------------------------------- append file Else If [$action = "append file"] Set Variable [$result; Value:smFileAppend( $patha ; $data )] #-------------------------------------------------- copy file Else If [$action = "copy file"] Set Variable [$result; Value:smFileCopy ( $patha ; $pathb )] #-------------------------------------------------- delete file Else If [$action = "delete file"] Set Variable [$result; Value:smFileDelete( $patha )] #-------------------------------------------------- file exists Else If [$action = "exists"] Set Variable [$result; Value:smFileExists ( $patha )] #-------------------------------------------------- create folder Else If [$action = "create folder"] Set Variable [$result; Value:smFileFolderCreate ( $patha )] #-------------------------------------------------- select folder // Else If [//Not COMPATIBLE ON SERVER $action = "select folder"] // Set Variable [$result; Value:<Function Missing> ( $patha ; $data ) //Starting path / prompt] #-------------------------------------------------- folder size Else If [$action = "folder size"] Set Variable [$result; Value:smFileFolderSize ( $patha )] #-------------------------------------------------- list folder Else If [$action = "list folder"] Set Variable [$result; Value:smFileListDirectory ( $patha )] #-------------------------------------------------- file modification Else If [$action = "file modification"] Set Variable [$result; Value:smFileModificationDate ( $patha )] #-------------------------------------------------- move file Else If [$action = "move file"] Set Variable [$result; Value:smFileMove ( $patha ; $pathb )] #-------------------------------------------------- read file Else If [$action = "read file"] Set Variable [$result; Value:smFileRead ( $patha )] #-------------------------------------------------- write file Else If [$action = "write file"] Set Variable [$result; Value:smFileWrite( $patha ; $data ) //Starting path / prompt] #-------------------------------------------------- zip file Else If [$action = "zip file"] Set Variable [$result; Value:smFileZip ( $patha ; $pathb )] End If # #============================================== Exit Script [$result]

HELP! I have put together an import script that uses ScriptMaster for a couple things like check for the existence of the fuel, create a folder, move it and zip it. When running this on the desktop, it works flawlessly, Now I need it to work from the server as a scheduled script. I can confirm that the plugin is indeed loading and loading all necessary scripts for the various functions, all ok. But the buck stops there. If I ask, does the files exist? I get back, NO. If I check from the data viewer on the desktop for a copy of the file in my documents folder--YES....on the sever documents? NO. I changed the permission on the files in case that was the issue...No difference. So--do I have the files in the right place, you ask? /MacHD/Library/FileMaker Server/Documents/ uh--check! I have tried checking with both the above path and just /Library/FileMaker Server/Documents/ and neither work. I am using FMSA12. And since you can't run a debugger in the server environment I am sending back emails to my self with some status updates as to the script progress. I can confirm that the plugin is definitely loaded and all required modules are loaded.