Filemaker Consulting

Suppose there is a table called Sales , which has SalesID:-Autoenter serial number  .There are records having SalesID :- 1,2,3,4,5…… etc .

If some user deletes some records from Sales , how shall we find the Sales records which are missing ?

For Example :- Records present having SalesID:- 1,2,3,5,7,10,11,12,14 ….etc

We need to find  missing records SalesID:-4,6,8,9,13 ….etc

This can be accomplished via custom function as well as through scripting. As the custom function recursive call is 50000 only . I have created the function in script so that more than 50000 records can be processed.

/*………………………………………………..

UniqueList(list1;count1;newlist)

 list1= the list of the datas from which the new unique list to be obtained.

count1=always 1.

newlist=”” (always null)

Subcustom function to be used :-

SearchLine(text1;count1;searched1)

text1=the list of datas from which a searched line to be compared

count1=always 1

searched1= the line to be searched.

Below is the Custom function for UniqueList(list1;count1;newlist)

…………………………………………………..*/

Let (
[ list=list1;

total_values=ValueCount ( list );

value=GetValue ( list ; count1 )
];

If (  total_values  ≥ count1 and total_values >0 ;

Case (
SearchLine(newlist;1;value) ≠ 1;  UniqueList(list;count1+1; If ( count1=1 ; value ; newlist & “¶”  & value )  ) ;UniqueList(list;count1+1;newlist )

)
;newlist)

)

//———————finished——————————–

——————————————————————————————-

//Please put SearchLine(text1;count1;searched1) as another custom function.

Let (

[   data=text1;

total = ValueCount ( data ) ;
value =  GetValue (  data ; count1 ) ;
count1= count1 + 1 ];

Case ( count1  < total + 2  ;
If (value= searched1 ;1 ;SearchLine(text1;count1;searched1) ))

)

————————————————————————————-

Example:- UniqueList(list1;count1;newlist)

1.UniqueList (“abc def”  & “¶”  &  “abc def” & “¶” &  “mno pqr”  & “¶” & “mno pqr” & “¶” & “abc def” & “¶” & “qwr rew”; 1 ; “” ) =

abc def
mno pqr
qwr rew

2.UniqueList (“abc def”  & “¶”  &  “abc def” & “¶” &  “mno pqr”  & “¶” & “mno pqr” & “¶” & “abc def” ; 1 ; “” ) =

abc def
mno pqr

Example:- SearchLine(text1;count1;searched1)

1.SearchLine (“abc def”  & “¶”  &  “abc def” & “¶” &  “mno pqr”  & “¶” & “mno pqr” & “¶” & “abc def” & “¶” & “qwr rew”; 1 ; “abc def” ) = 1

2.SearchLine (“abc def”  & “¶”  &  “abc def” & “¶” &  “mno pqr”  & “¶” & “mno pqr” & “¶” & “abc def” & “¶” & “qwr rew”; 1 ; “bert wert” ) = “”

3.SearchLine (“abc def”  & “¶”  &  “abc def” & “¶” &  “mno pqr”  & “¶” & “mno pqr” & “¶” & “abc def” & “¶” & “qwr rew”; 1 ; “mno pqr” ) = 1

/*
Created By Jibardhan Patel
Purpose:-To search a value is duplicated (more than 1 instance)

Function Name- CountOfValue(text1;count1;searched1)

Parameters:-text1 = list of values from which a value needs to be count
count1=always 1
searched1=the value that needs to be counted

Example:-  1. CountOfValue(“abc”&”¶”&”def”&”¶”&”abc”&”¶”&”abc”&”¶”&”mno”;1;”abc”) = 3
2. CountOfValue(“abc”&”¶”&”def”&”¶”&”abc”&”¶”&”mno”;1;”abc”) = 2
3. CountOfValue(“abc”&”¶”&”def”&”¶”&”abc”&”¶”&”abc”&”¶”&”mno”;1;”mno”) = 1

*/

Let (

[   data=text1;

total = ValueCount ( data ) ;
value =  GetValue (  data ; count1 ) ;
count1= count1 + 1 ];

Case ( count1  < total + 2  ;
If (value= searched1 ;1+ CountOfValue(text1;count1;searched1) ;CountOfValue(text1;count1;searched1) )

)
)

Sometimes we need to find values in the form of a list from a current foundset .

Some people uses looping to get the values in the foundset.
Below is a custom function ,from which the data can be retrieved in the form of List from a foundset.
————————————————————————–
/*

Created By :Jibardhan Patel
Custom Function Name:- ListFieldFoundSet(fieldname;found_count;one)

fieldname = name of the field from which a list to be created.
found_count = Get ( FoundCount )  of the layout.
one=1 (always 1 ; it doesnot take part in calculation)

This function should be called on the Layout on which we need to to retrieve the values from a foundset.
*/
Let (
[  value =  GetNthRecord ( fieldname ; one );
value1= If (value=”?” ; “” ; value )

];

Case (one < found_count ;
If (value1=”” ;

ListFieldFoundSet(fieldname;found_count;one+1) ;

value  & “¶”  & ListFieldFoundSet(fieldname;found_count;one+1) )

; value )

)

This is a simple custom function on which the number of parameters are only 2 and no other parameters have been added to it.
It doesnot also depend on any other custom function.

Custom Funtion Name:-PlacingZeros(num;zeros )

/*———————————————-
Created By: Jibardhan Patel
Example:-
PlacingZeros ( 23 ;4 ) = 0023
PlacingZeros ( 23 ;5  ) = 00023
PlacingZeros ( 23 ;3  ) = 023
PlacingZeros ( 23 ;2  ) = 23
PlacingZeros ( 23 ;1  ) = ERROR

There is no limit restriction given to the number of zeros to be appended.
Developer can add a limitation restriction if required.

————————————————*/

Let (

[

num1=GetValue (num ; 1 );

num2= GetValue (num ; 2 );

num3=If ( IsEmpty ( num2 ) ; “0” &   num1 ;  “0” & num2  );

len1=  Length(num1);

len2=  Length(num2)

];

Case (

len1 =zeros;num1;

len1  > zeros;”ERROR” ;

len2  > zeros-1 ;num2 ;  PlacingZeros(num1 & “¶” &  num3 ;zeros )

)

)

There are a number of plugins in the market which satisfies the work of moving files from Local to FTP.
Another way , is to create a batch file which would run on Server/Local to get the work done.
-From local , users need to use the command Send Event[BatchFileName] script step.
-From server, users need to put the batch file on C:\Program Files\FileMaker\FileMaker Server\Data\Scripts or the Scripts folder where the FileMaker server is installed.
From Admin Console , a script schedule as FileMaker Script > System Level Script > BatchFileName can be created.
Run the schedule and the file shall get transferred to FTP.
Batch File Name
——————————————-
@echo off
echo user patel> ftpcmd.dat
echo patel2014>> ftpcmd.dat
echo bin>> ftpcmd.dat
echo put “C:\Program Files\FileMaker\FileMaker Server\Data\Documents\ExcelReportExample.xlsx”>> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat myserver.testing.com
del ftpcmd.dat
——————————————–

FTP host name:-myserver.testing.com
UserName:-patel
Password:-patel2014

Location of the file to be transferred:- C:\Program Files\FileMaker\FileMaker Server\Data\Documents\ExcelReportExample.xlsx

/*

Function Name :- MonthsBetweenMonths(firstDate;lastDate)
———————————————————–
firstDate and lastDate should be non empty

Date Format :- mm/dd/yyyy

Example:- firstDate = “5/1/2014″ , lastDate =”5/20/2014” , result =0

firstDate = “5/1/2014″ , lastDate =”4/16/2014” , result =-1

firstDate = “5/1/2014″ , lastDate =”2/8/2014” , result =-2

firstDate = “5/1/2014″ , lastDate =”6/12/2014” , result =1

firstDate = “5/1/2014″ , lastDate =”7/12/2014” , result =2

firstDate = “5/1/2014″ , lastDate =”8/12/2014” , result =3

*/

If ( not IsEmpty ( firstDate ) and not IsEmpty ( lastDate  );

If ( GetAsDate (  lastDate )  ≥  GetAsDate (  firstDate )  ;

Let (

[  year_last=Year ( lastDate ) ;

year_first =Year (firstDate ) ;

month_first = Month ( firstDate );

month_last = Month ( lastDate );

month_total= Case (

year_last = year_first  and  month_last  ≥  month_first ;  month_last   – month_first  ;

year_last = year_first +1 ; month_last + (12- month_first) ;

year_last – year_first  ≥ 2 ; (year_last – year_first)*12 +  (12- month_first) + month_last  ;

) ];

month_total

)

;

Let (

[  year_last=Year ( lastDate ) ;

year_first =Year (firstDate ) ;

month_first = Month ( firstDate );

month_last = Month ( lastDate );

month_total= Case (

year_last = year_first and   month_last  <  month_first; month_first- month_last    ;

year_last = year_first-1 ; 12- month_last  + month_first  ;

year_first – year_last  ≥ 2 ; ( year_first – year_last )*12  + 12 – month_last +1   ;

) ];

“-” & month_total

)

)

;””)

Leisure time…………….

https://www.dropbox.com/s/wr8vis9nskz87u2/CrossNNuts.zip?dl=0

I have created a Date Filter mechanism for a number of parameters .Developers can directly copy the script (no editing required) and can use in their scripts .

https://www.dropbox.com/s/ct8004sawx1zxop/DateFilter.zip?dl=0

//Created By Jibardhan Patel
//Date of Creation:12.12.2013
//Date of Modification:12.12.2013
//Purpose:-To search a value whether it is present in a List or not , if the value will be found then 1 will be returned.

SearchText(text1;count1;searched1) )

text1=list if texts , count1=1,searched1= text to be searched

Let (

[   data=text1;

total = ValueCount ( data ) ;
value =  GetValue (  data ; count1 ) ;
count1= count1 + 1 ];

Case ( count1  < total + 2  ;
If (value= searched1 ;1 ;SearchText(text1;count1;searched1) ))

)