Aatrix Mac P&L Modifing Data
Aatrix Mac P&L Modifing Data
- Subject: Aatrix Mac P&L Modifing Data
- From: John Haley <email@hidden>
- Date: Mon, 22 Jul 2002 23:34:30 -0500
I am not very good with original AppleScript. I will take what I can
find and change it to my needs, but I cat't find much for Mac P&L. I
want to modify customer information. I have tried:
tell application "Aatrix MacP&Lb"6.3.1 Client 4"
tell Database 1
tell Table "Customers"
set (Value of Cell "Name" of (every Record whose Value of
Cell "CodeNumber" = "A2306")) to "Test Company"
end tell
end tell
end tell
which actually does change the name, but it takes about 4 minutes and it
errors out with a time out error.
The scripts I have found, one which creates an account and one which
pulls the information from the account, are bellow.
-- variable list
set vCustID to ""
set vName to ""
set vAddress to ""
set vCity to ""
set vState to ""
set vZip to ""
set vCountry to ""
set vPhone to ""
set vFax to ""
set vSalesAcct to ""
set vTaxAcct to ""
set vTaxRate to 0
set vDiscount to 0
set vTerms to "Net 30"
set vOpenAccount to true
-- get customer info from FileMaker
tell application "FileMaker Pro"
tell current record
--set the account status
set tempOpenAccount to cellValue of cell "Open Balance Forward"
if tempOpenAccount is not "Open" then
set vOpenAccount to false
else
set vOpenAccount to true
end if
-- set the rest of the variables
copy cellValue of cell "Customer ID" to vCustID
copy cellValue of cell "Customer Name" to vName
copy cellValue of cell "Discount Rate" to vDiscount
copy cellValue of cell "Sales Tax Rate" to vTaxRate
copy cellValue of cell "Customer Address" to vAddress
copy cellValue of cell "Customer City" to vCity
copy cellValue of cell "Customer State" to vState
copy cellValue of cell "Customer ZIP Code" to vZip
copy cellValue of cell "Customer Country" to vCountry
copy cellValue of cell "Customer Phone" to vPhone
copy cellValue of cell "Customer Fax" to vFax
copy cellValue of cell "GL Sales Acct Number" to vSalesAcct
copy cellValue of cell "GL Tax Acct Number" to vTaxAcct
end tell
end tell
-- change the tax and discount rates into numbers
repeat
if vTaxRate = "" then set vTaxRate to text returned of (display
dialog "Please enter a Sales Tax Rate for customer " & vCustID & ":"
default answer 0 buttons {"Cancel", "OK"} default button "OK")
if vTaxRate contains "%" then set vTaxRate to characters 1 thru
((number of characters in vTaxRate) - 1) of vTaxRate
try
set vTaxRate to vTaxRate as string
set vTaxRate to vTaxRate as number
exit repeat
on error
end try
end repeat
repeat
if vDiscount = "" then set vDiscount to text returned of (display
dialog "Please enter a Discount Rate for customer " & vCustID & ":"
default answer 0 buttons {"Cancel", "OK"} default button "OK")
if vDiscount contains "%" then set vDiscount to characters 1 thru
((number of characters in vDiscount) - 1) of vDiscount
try
set vDiscount to vDiscount as string
set vDiscount to vDiscount as number
exit repeat
on error
end try
end repeat
tell application "Aatrix MacP&Lb"6.2"
try --checks to see if there is a database open
get the Name of Database 1
on error --if there is no datatabse open, select one to open
choose file with prompt "Please select a MacP&L database to
open:" of type "APB2" -- "APB2" only lists MacP&L databases
set fileName to result
Open fileName
end try
tell Database 1
-- make sure there are sales and tax accounts and they exsit in MacP&L
tell Table "GL Accounts"
repeat
if vSalesAcct = "" then set vSalesAcct to text
returned of (display dialog "Please enter a Sales Account for
customer " & vCustID & ":" default answer 4500 buttons {"Cancel", "OK"}
default button "OK") -- this will keep repeating until a Sales Account
is entered. It defaults to the main sales account in the Booksmith
sample company file.
try
(Value of Cell "Name" of (every Record whose
Value of Cell "Account #" = vSalesAcct))
exit repeat
on error
display dialog "A Sales Account with a number
of " & vSalesAcct & " doesn not exist in MacP&L."
set vSalesAcct to ""
end try
end repeat
repeat
if vTaxAcct = "" then set vTaxAcct to text returned
of (display dialog "Please enter a Tax Account for customer " &
vCustID & ":" default answer 2360 buttons {"Cancel", "OK"} default
button "OK") -- this will keep repeating until a Tax Account is entered.
It defaults to the main sales account in the Booksmith sample company
file.
try
(Value of Cell "Name" of (every Record whose
Value of Cell "Account #" = vTaxAcct))
exit repeat
on error
display dialog "A Tax Account with a number
of " & vTaxAcct & " doesn not exist in MacP&L."
set vTaxAcct to ""
end try
end repeat
end tell
-- Add the customer to MacP&L
tell Table "Customers"
repeat
if vCustID = "" then set vCustID to text returned of
(display dialog "Please enter the ID number for this customer:" default
answer "" buttons {"Cancel", "OK"} default button "OK")
--make sure the customer exists in MacP&L
try
(Value of Cell "Name" of (every Record whose
Value of Cell "CodeNumber" = vCustID))
display dialog "MacP&L already has a customer
with an ID of " & vCustID & "." buttons "OK" default button "OK"
set vCustID to "" --reset the customer ID
on error
exit repeat
end try
end repeat
-- once we have a valid ID, add the rest of the information
set theRecord to {vCustID, vName, vSalesAcct, vTaxAcct,
vTerms, vAddress, " ", vCity, vState, vZip, vCountry, vPhone,
vFax, " ", " ", 0.0, 0.0, vTaxRate, vDiscount, true}
try
Create New Record At end With Data theRecord
display dialog vName & " has been successfully added
to MacP&L as a customer."
on error errMsg number errNbr
display dialog errMsg & return & return & "(" & errNbr & ")"
end try
end tell
end tell
end tell
-- variable list
set vCustID to ""
set vName to ""
set vAddress to ""
set vCity to ""
set vState to ""
set vZip to ""
set vCountry to ""
set vPhone to ""
set vFax to ""
set vSalesAcct to ""
set vTaxAcct to ""
set vTaxRate to 0
set vDiscount to 0
set vOpenAccount to true
-- get customer id and customer name from FileMaker
tell application "FileMaker Pro"
tell current record
set vCustID to cellValue of cell "Customer ID" -- stores the
customer ID
end tell
end tell
-- get customer information from MacP&L, based on ID and Name from
FileMaker
tell application "Aatrix MacP&Lb"6.2"
try --checks to see if there is a database open
get the Name of Database 1
on error --if there is no datatabse open, select one to open
choose file with prompt "Please select a MacP&L database to
open:" of type "APB2" -- "APB2" only lists MacP&L databases
set fileName to result
Open fileName
end try
tell Table "Customers" of Database 1
repeat
if vCustID = "" then set vCustID to text returned of
(display dialog "Please enter the ID number for this customer:" default
answer "" buttons {"Cancel", "OK"} default button "OK")
--make sure the customer exists in MacP&L
try
(Value of Cell "Name" of (every Record whose Value of
Cell "CodeNumber" = vCustID))
exit repeat
on error
display dialog "MacP&L does not have a customer with
an ID of " & vCustID & "." buttons "OK" default button "OK"
set vCustID to "" --reset the customer ID
end try
end repeat
-- once we have a valid ID, set the rest of the variables
set vName to (Value of Cell "Name" of (every Record whose
Value of Cell "CodeNumber" = vCustID))
-- now we need to combine the address fields for use in FileMaker
set tempAddr1 to (Value of Cell "Addr Ln1" of (every Record
whose Value of Cell "CodeNumber" = vCustID)) --this gets line 1 of the
address in MacP&L
set tempAddr2 to (Value of Cell "Addr Ln2" of (every Record
whose Value of Cell "CodeNumber" = vCustID)) --this gets line 2 of the
address in MacP&L
-- if there is a line 2 for the address, concatenate them and
set vAddress
if tempAddr2 b "" then
set vAddress to tempAddr1 & return & tempAddr2
else
set vAddress to tempAddr1
end if
set vCity to (Value of Cell "City" of (every Record whose
Value of Cell "CodeNumber" = vCustID))
set vState to (Value of Cell "State" of (every Record whose
Value of Cell "CodeNumber" = vCustID))
set vZip to (Value of Cell "Zip" of (every Record whose Value
of Cell "CodeNumber" = vCustID))
set vCountry to (Value of Cell "Country" of (every Record
whose Value of Cell "CodeNumber" = vCustID))
set vPhone to (Value of Cell "Phone" of (every Record whose
Value of Cell "CodeNumber" = vCustID))
set vFax to (Value of Cell "Fax" of (every Record whose Value
of Cell "CodeNumber" = vCustID))
set vSalesAcct to (Value of Cell "Sales Account" of (every
Record whose Value of Cell "CodeNumber" = vCustID))
set vTaxAcct to (Value of Cell "Tax Account" of (every Record
whose Value of Cell "CodeNumber" = vCustID))
set vTaxRate to (Value of Cell "Taxable" of (every Record
whose Value of Cell "CodeNumber" = vCustID))
set vDiscount to (Value of Cell "Discount" of (every Record
whose Value of Cell "CodeNumber" = vCustID))
set vOpenAccount to (Value of Cell "Open Account" of (every
Record whose Value of Cell "CodeNumber" = vCustID))
end tell
end tell
tell application "FileMaker Pro"
tell current record
if cellValue of cell "Customer Name" is not "" then
display dialog "OK to overwrite this record?" buttons
{"Cancel", "OK"} default button "OK"
end if
set cellValue of cell "Customer ID" to vCustID
set cellValue of cell "Customer Name" to vName
set cellValue of cell "Discount Rate" to vDiscount
set cellValue of cell "Sales Tax Rate" to vTaxRate
set cellValue of cell "Customer Address" to vAddress
set cellValue of cell "Customer City" to vCity
set cellValue of cell "Customer State" to vState
set cellValue of cell "Customer ZIP Code" to vZip
set cellValue of cell "Customer Country" to vCountry
set cellValue of cell "Customer Phone" to vPhone
set cellValue of cell "Customer Fax" to vFax
set cellValue of cell "GL Sales Acct Number" to vSalesAcct
set cellValue of cell "GL Tax Acct Number" to vTaxAcct
--set the account status
if vOpenAccount is false then
set cellValue of cell "Open Balance Forward" to "Balance Forward"
else
set cellValue of cell "Open Balance Forward" to "Open"
end if
end tell
end tell
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.