Database Events Query

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?


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.

  1. Get parallel lists of model numbers, prices, and dates from Database Events.
  2. Merge the lists into a list of model/price/date sets.
  3. Sort this list of lists by model number, subsorting by date.
  4. 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.
  5. 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"
		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
								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