2 ways come to mind:
1) GetNthRecord() will get values from records forward or backwards.
2) ExecuteSQL(), calculating where PurchaseDate < CurrentPurchaseDate and limiting to one result when sorting by descending date order.
Thank you for jumping right on this!
Through an ExecuteSQL, can the data (in this case the previous purchase date), be written a new field: PreviousPurchaseDate?
If your goal is to auto-enter a field in a new subsequent record, the easiest way to do that is auto-enter it with a calculation. In which case yes, either of the 2 ways I posted could be used to calculate that value.
You can also use a sorted self join based on Customer ID and date to access the immediately previous date for a given customer.
Invoices::CustomerID = InvoicesCustomerDate::CustomerID AND
Invoices::DatePosted > InvoicesCustomerDate::DatePosted
Sort InvoicesCustomerDate by DatePosted in Descending order and
Will return the date of the immediately previous Invoice for a given customer. This assumes one invoice per customer per day.
This is not necessarily a better option than ExecuteSQL.
SELECT DatePosted FROM Invoices
WHERE CustomerID = ? AND
DatePosted > ?
ORDER BY DatePosted DESC
FETCH FIRST 1 ROW ONLY" ;
"" ; "" ; Invoices::CustomerID ; Invoices::DatePosted )
You might also use Get ( CurrentDate ) in place of Invoices::DatePosted as the second optional parameter.
Thanks, Mike. We have a vague connection - in 1982, I was a "lounge singer" at the Holiday Inn across the street from the Ohio State football stadium - right at the beginning of football season. I know from nuthin' about sports, but I learned "Hang on Sloopy" right quick.
Now, if only I could master FileMaker:)
Thank you for the in-depth response! I may lick this yet!
I grew up in Cleveland, and wasn’t a resident of Columbus until circa 2003. I believe that holiday inn was converted into dorms as well. Mostly I just try to avoid campus, but the football games are something else.
Another vague connection - my wife is from Painesville (otherwise known as the "aptly named village"). I'm in the land of cleves fairly often. Thanks again.
That’s a closer connection, people from Lakewood (where I grew up) regularly went there for a famous haunted house in the fall every year. Best of luck learning, and feel free to look me up if you ever come through cbus.
1 of 1 people found this helpful
One Developer's Note:
To avoid confusion, I composed the above query in as simple a format as possible. I don't actually write my queries in the form that I posted here as it encloses field and table occurrence names inside quotation marks. If you write your queries in this format, any field or table occurrence name change made via Manage Database, will cause this expression to return a ?--indicating a syntax error.
The method that I use meets the following standards that I set for myself:
a) No field or table occurrence names are "hard coded" inside quotation marks
b) The original query is kept intact and not broken up with lots of ampersands, quotes and custom function calls
c) It is something that I can write very quickly without a lot of laborious copy/pasting or careful detail checking to make sure that my basic data entry is correct.
For those interested, you can find the custom functions that I use and how I use them in the "button bars as Value Lists" example in:
(See the "Flexible SQL" tab on that layout.)