Forgot password? | Forgot username? | Register

Querying a particular row in a table

Querying a particular row in a table

I'd like to see support within the EMu client for searching a particular row of a table field.  For example, we use our Inventory Status field to record the reported inventory status of objects through time in reverse chronological order, i.e., with the most recent inventory status at top.  So an object that was missing in the past but was recently found might look like this:

Status   Date
Found   2012
Missing 2010

while an object that was found in the past but is now missing might look like this:

Status   Date
Missing 2012
Found 2010

There is no straightforward way to query for only those objects that are currently missing, since querying for "missing" would find both of the above objects.  One solution might be to create a special "Current Inventory Status" field (similar to our "Current Scientific Name" field in Taxonomy) but a simpler and more generally useful approach would be to add support within the client for querying only a particular row of a table field, in this case the first row.

Thanks,
Tom Trombone
AMNH

Thomas Trombone
Data Manager, Ornithology
useravatar
Offline
54 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Querying a particular row in a table

Hi Thomas - we actually change the location of missing items to "TBL - To Be Located". Then when it is found again, we update the location to where it really is. We can then (though there is no policy dictating we do so) delete the internal movement history entry of TBL. A simple query fro all records located to TBL is qucik and easy. Couple this with updating your inventory table, for the inventory history, and I think your problem is solved.

That said, the ability to limit searches to just the 1st row of a nested table would be quite handy, I think.

Mark

Edited by: Mark Bradley - 19-Feb-13 14:34:17

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Querying a particular row in a table

Thanks, Mark; that's a good idea.  I will likely implement something similar here.  I know it is possible to edit the query text via "Show Search" to include the statement "rownum=1" which will return only records with the query term in the first row of a table field, but such a query seems always to take a very long time to run.  A dedicated "first row value" would certainly speed up retrieval of just those records of interest to me.

Tom
AMNH

Thomas Trombone
Data Manager, Ornithology
useravatar
Offline
54 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic