I am sure I will be embarrassed by how easy the solution is but …
How would I query a database to find all records whose value of field ABC is equal?
Hi,
what value?
If you know the value, you could do
tell application "Database Events"
tell database "myDatabase"
set filteredRecords to every record whose value of field "ABC" is "whatever"
end tell
end tell
but if you don’t know the value, you have to go thru all records and create a list of the different values and their occurrence
Thanks Stefan,
I don’t know the value. I am trying to compare prices over time. The fields are Model, Date and Price. I wanted to set up a query for items whose model is the same and most recent price is lower than older price. I am doing some reading about sqlite3 now, seems like it may be better suited for the query.
This works.
- Get parallel lists of model numbers, prices, and dates from Database Events.
- Merge the lists into a list of model/price/date sets.
- Sort this list of lists by model number, subsorting by date.
- Go through the sorted list. At the end of each run of a particular model number, compare the last two prices in the run. If the last one’s less than the one before it, append the relevant details to a results list.
- Return the results list.
Steps 2 to 5 actually take less time than step 1.
on main()
-- Script object containing referenceable properties for fast list access and a comparison handler for a custom sort.
script o
property modelList : missing value
property priceList : missing value
property dateList : missing value
property sortingList : {}
property priceDrops : {}
-- Comparison handler. List a is "greater" than list b if its first item (here a model number) is greater than b's or those items are equal and a's last item (a date) is greater than b's.
on isGreater(a, b)
set a1 to beginning of a
set b1 to beginning of b
((a1 > b1) or ((a1 = b1) and (end of a > end of b)))
end isGreater
end script
set dbPath to (path to documents folder as text) & "Databases:Home Depot.dbev"
tell application "Database Events"
launch
set quit delay to 0
if not (database "Home Depot" exists) then open file dbPath
-- Get parallel lists of all model numbers, prices, and dates.
tell database "Home Depot"
set {o's modelList, o's priceList, o's dateList} to {value of field "Model", value of field "Price", value of field "Date"} of records
end tell
end tell
-- Merge the lists into a list of lists, each sublist being a matching model/price/date set.
set recordCount to (count o's modelList)
repeat with i from 1 to recordCount
set end of o's sortingList to {item i of o's modelList, item i of o's priceList, item i of o's dateList}
end repeat
-- Sort the list of lists by model number, sub-sorting by date.
CustomShellSort(o's sortingList, 1, -1, {comparer:o})
-- Work through the sorted list of lists. At the end of each run of equal model numbers, compare the last two prices in the run.
set i to 1
set currentModel to beginning of beginning of o's sortingList
repeat with j from 2 to recordCount
set thisModel to beginning of item j of o's sortingList
if (thisModel comes after currentModel) then
-- A different model number. Compare the last two prices (if more than one) for the previous model and insert details of any price drop into o's priceDrops.
if (j - i > 1) then checkPrices(j - 1, o)
-- Reset for a run of the model number just reached.
set i to j
set currentModel to thisModel
end if
end repeat
if (j > i) then checkPrices(j, o) -- Check the run in progress at the end of the list.
return o's priceDrops
end main
-- Compare the price in the indexed list in o's sortingList with that in the list before. If it's less, append relevant details to o's priceDrop list.
on checkPrices(j, o)
set latestDetails to item j of o's sortingList
set previousDetails to item (j - 1) of o's sortingList
set latestPrice to item 2 of latestDetails
set previousPrice to item 2 of previousDetails
considering numeric strings
if (latestPrice < previousPrice) then set end of o's priceDrops to {model:beginning of previousDetails, |previous date|:end of previousDetails, |latest date|:end of latestDetails, |previous price|:previousPrice, |latest price|:latestPrice}
end considering
end checkPrices
-- Customisable Shell sort. Algorithm: Donald Shell. Implementation: Nigel Garvey.
on CustomShellSort(theList, l, r, customiser)
script o
property comparer : me
property slave : me
property lst : theList
on shsrt(l, r)
set inc to (r - l + 1) div 2
repeat while (inc > 0)
slave's setInc(inc)
repeat with j from (l + inc) to r
set v to item j of o's lst
repeat with i from (j - inc) to l by -inc
tell item i of o's lst
if (comparer's isGreater(it, v)) then
set item (i + inc) of o's lst to it
else
set i to i + inc
exit repeat
end if
end tell
end repeat
set item i of o's lst to v
slave's shift(i, j)
end repeat
set inc to (inc / 2.2) as integer
end repeat
end shsrt
on isGreater(a, b)
(a > b)
end isGreater
on shift(a, b)
end shift
on setInc(a)
end setInc
end script
set listLen to (count theList)
if (listLen > 1) then
if (l < 0) then set l to listLen + l + 1
if (r < 0) then set r to listLen + r + 1
if (l > r) then set {l, r} to {r, l}
if (customiser's class is record) then set {comparer:o's comparer, slave:o's slave} to (customiser & {comparer:o, slave:o})
o's shsrt(l, r)
end if
return -- nothing.
end CustomShellSort
main()