Well it has been a long time since I’ve posted on the site.
I wish appear New year to everybody!
=========
I am trying to get an Applescript program and FileMaker Pro working together.
The programs is to update a FMP cell “Status” to “B” - this works
The program is to loop through a text string and extract characters (set nextpar) - this does not work
I’ve included a small applescript program (at the beginning) which does what the other part of the program should do.
Could someone tell me what is wrong with my syntax (set nextpar to (text theStart thru theEnd of FMPrec)) or doest it have to do with the line (set the_record to (a reference to record i)
Regards!
Daniel
set theStart to 1
set theEnd to 4
set FMPrec to "123456781233"
repeat with i from 1 to 3
set nextpar to (text theStart thru theEnd of FMPrec)
display dialog "Nextpar" & nextpar
set theStart to theStart + 4
set theEnd to theEnd + 4
end repeat
set theStart to 1
set theEnd to 4
tell application "FileMaker Pro Advanced"
tell database "wPZV"
tell table "CustomerOrder"
try
tell (every record whose cell "InvoiceN" is "123450" and cell "IndexN" is "1")
set cell "Status" to "B"
set recordSet to {}
repeat with i from 1 to (count of records)
set the_record to (a reference to record i)
set FMPrec to cell "OrderN°" of the_record
display dialog "aaaa" & FMPrec & "aaaa" & "-" & theStart & "-" & theEnd
set nextpar to (text theStart thru theEnd of FMPrec)
set theStart to theStart + 4
set theEnd to theEnd + 4
end repeat
return recordSet --list of record IDs
end tell
on error
display dialog "Cannot find the record"
end try
end tell
end tell
end tell
return
unfortunately the code is not clear enough to understand exactly what you’re going to accomplish
There are a couple of problems in your script.
First of all, a long tell block like tell (every record whose.) is very dangerous, because each FM command affects
all filtered elements, which cause errors in many cases
It’s better to write something like
.
set filteredRecords to every record whose cell "InvoiceN" is "123450" and cell "IndexN" is "1"
set cell "Status" to "B"
set recordSet to {}
repeat with i from 1 to (count of filteredRecords)
set the_record to (a reference to item i of filteredRecords)
.
An easier way to get consecutive substrings of a string is
set FMPrec to "123456781233"
set stringLength to length of FMPrec
repeat with i from 1 to stringLength by 4
if stringLength - i < 4 then
-- in case the length of the string is not divisible by 4
set nextpar to (text i thru -1 of FMPrec)
else
set nextpar to (text i thru (i + 3) of FMPrec)
end if
display dialog "Nextpar" & nextpar
end repeat
If the syntax invoiceIndexOrdeNoStatus is always the same
this code extracts Number and Status much easier without any repeat loop
set theText to "012345 1 0101 C"
set {TID, text item delimiters} to {text item delimiters, space}
set {OrdeNo, Status} to text items -2 thru -1 of theText
set text item delimiters to TID
display dialog "OrdeNo: " & OrdeNo & return & "Status: " & Status
No the syntax InvoiceIndexOrdeNoStatus will never be the same. I will want to access the information for a given Invoice and Index.
This is why at this time I am doing Invoice 012345 and Index 1, next time in will be doing Invoice 012344 and Index 2, followed by 012345 and Index 2. It all depends when OrderNo areavailables.
In FMP, if I perform a Find command for Invoice 012345 and Index 1. The system will display x records. I then need to go through x records and get the OrderNo.
This is why I am looping through x number of records repeat with i from 1 to (count of records)
I’ve used your information and got the script to behave as wanted. Unfortunately, only when used as a stand alone applescript application. I’ve ported over the script within filemaker pro and it does not work. I seem to have a problem with the tell (every record whose cell… and the set filteredRecords to every record whose…
In the meantime, I had to keep the tell (every record whose cell… command because this seemed the only way to get the status changed for all records equal to NoInvoice.
Stefan, when you say
Is it better the way I am using it at this moment. I have a Tell, the command and an End tell
--set NoInvoice to cell "N°Invoice" of current record
--set Index Order to cell "SN°Order" of current record
set NoInvoice to "123450"
set IndexOrder to "1"
set the_list to {}
tell application "FileMaker Pro Advanced"
tell database "wPZV"
tell table "CustomerOrder"
try
tell (every record whose cell "N°Invoice" is NoInvoice and cell "SN°Order" is IndexOrder)
set cell "Status" to "B"
end tell
set filteredRecords to every record whose cell "N°Invoice" is NoInvoice and cell "SN°Order" is IndexOrder
set recordSet to {}
repeat with i from 1 to (count of filteredRecords)
set the_record to (a reference to item i of filteredRecords)
set theOrder to (text 3 thru 3 of the_record)
set the_list to the_list & theOrder
end repeat
on error
display dialog "Cannot find the record"
end try
end tell
end tell
end tell
to avoid filtering the records twice, which is very expensive, you can write
.
tell (every record whose cell "N°Invoice" is NoInvoice and cell "SN°Order" is IndexOrder)
set cell "Status" to "B"
set filteredRecords to it
end tell
set recordSet to {}
.
Thanks again for your help, I will modify the script accordingly. When you mention, which is very expensive, I guess you mean if the database has a large volume it would take lot of time to go through, then doing this twice only makes it worst?
Would you have an idea why the statement “tell (every record whose cell “N°Invoice” is NoInvoice and cell “SN°Order” is IndexOrder)” does not work if executed within FMP.
expensive means much consumption of time and resources
because this
.
tell (every record whose cell "InvoiceN" is "123450" and cell "IndexN" is "1")
repeat with i from 1 to (count of records)
set the_record to (a reference to record i)
.
is the same as
.
repeat with i from 1 to (count of records of (every record whose cell "InvoiceN" is "123450" and cell "IndexN" is "1"))
set the_record to (a reference to record i of (every record whose cell "InvoiceN" is "123450" and cell "IndexN" is "1"))
.
Doest it mean it will never work inside FMP? or if I used instead:
it would work!
How Is it possible to make an element record in the record class? I am sorry, but this is difficult for me to understand. This is probably one of the fundamentals that I have not been able to catch yet.