If I assign an EX TANK, I need to find the previous record containing that tank in the TO tank,
I gather that you need to find a record in the Simple Operations table?
And this needs to be the "previous record" where the TO tank matches teh EX Tank of the current record?
And what data in your table identifies the "Previous Record"? (I wouldn't rely on the unsorted "creation order" to find the previous record. If you have to import the data--say into an upgraded copy of your database, the order in which you import the records becomes the new unsorted "creation order" and this may be a different order than what was in place originally.)
Do you have an ID field in Simple Operations that auto-enters a serial number?
Thank you for your answer Phil.
Yes, I am using the operation number to index on an auto-entered serial number, because between say Apr & May I may do 100+ other operations.
My search function will use TANK and BLEND CODE as a dual key, and the filter will be the maximum operation number (becasue we may take a wine out of tank and put it back into the same tank later).
One question you may be able to help me with: for a self join, do I (a) create an entirely new table called Simple OPeration Self-Join, complete with auto0serial indexing or (b) duplicate the original table in >relationships so that it doesn't appear in the tables list? Reading through other posts on self-joins, this is not very clear. In my pic above this was a table I made that was exactly the same as the orginal, minus auto-serial numbering (option a). Since then I have tried experimenting with option (b), but don't want to invest too much effort into a method that is incorrect.
a) is not a self join, it's a join to a completely different table. You want to do b) which is that you duplicate the Tutorial: What are Table Occurrences? in Manage | Database | Relationships and set up your links to it. You can then sort that relationship by the serial number field in descending order and then the first related record can be the previous record with the same matching data. You'll need to use ≠ to keep that from being the same record instead of the previous record.
Thank you! That explains a lot! A piece of poor user documentation I think. I'll bumble around with this for a while, but not close off the enquiry yet until I get it working. My next challenge is to get the script to find the right found set.
I wouldn't close the thread, ever. You never know when someone else will access this thread and have a useful suggestion to make.