Forgot password? | Forgot username? | Register

Searching for a specific row in a table field

Searching for a specific row in a table field

I want to search a table field for a value in the first row of that field. Does anyone know if this can be done, perhaps by editing the search text via "Show Search"? I can do it by exporting the values in the field and fiddling with them in Excel but I was hoping it might be done directly within the client.

Thanks,
Tom
Thomas J. Trombone
American Museum of Natural History

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: Searching for a specific row in a table field

I'd like to reignite the discussion on this topic of difficulty searching linkgrids/tables.  While there is a work-around using Show Search to search a specific row by its number (for example, only looking in row 1 for the search term), it's a clunky solution and only a partial one at that.

In our situation as fairly new EMu users, we mapped quite a bit of data to tables that we need to be able to extract (via search, report, export) based on two pieces of data being on the same row.  For example, in Notes, we need to be able to search a particular Notes Type for data in its matching Notes field, and we need to be able to extract this for reports.

We also use an Other Number table extensively in ecatalogue, and we need to be able to narrow our searches to look within only Field Number, Former Number, etc.

The work-around method for searching by row number doesn't work in these instances, because we don't know which row the Notes Type or Other Number type is on.  We really need to be able to search for a particular combination of Notes/Notes Type or similar.

Also, in some of the work-around solutions, the results come up as read-only, which requires using Excel or similar to extract the results and paste them back into a new EMu search to bring up the records needed - definitely unhandy.

Is anyone else out there struggling with this?  I'd like to gauge support for making this more functional, and to gather feedback to help KE find a solution that's workable.

Thanks,
Heather

Heather Thorwald
Denver Museum of Nature & Science

Heather Thorwald
Registrar
useravatar
Offline
6 Posts
Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Searching for a specific row in a table field

I agree with Heather that this would be really useful functionality to have available to us. It would make multi-field tables much more manageable; as it stands I worry about the ease of retrieving some of the data we store in these tables, as in the example involving a "Note" and a related "Kind of Note" described by Heather. Ideally such functionality should be incorporated into the client, but even having it available as a TexQL statement would be helpful and would greatly enhance the utility of such tables. (I see I first asked about this in 2007; it would be great to see the functionality of such tables finally beefed up!)

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: Searching for a specific row in a table field

Hi all,

I am bumping this thread to see if there have been any recent developments on this front. As Heather mentioned, not being able to search this way is a significant obstacle for many of our users. We see that some departments are not yet bothering to make complete catalog records because they know the data that would go in these note fields would be too cumbersome to retrieve with current the current search capabilities.

Any updates on this would be greatly appreciated.

Thanks!

Edited by: Sam Schiller - 09-May-14 01:16:04

Sam Schiller
Archivist, Bailey Library and Archives
Denver Museum of Nature & Science
samuel.schiller@dmns.org

Sam Schiller
Archivist
useravatar
Offline
3 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Searching for a specific row in a table field

Seven years and one month on, and the only useful way I have to do such searches is still to export the values and import them into Access (which I don't do very often.) I'll agree with your bump, Sam, and reiterate that adding such features to multi-value table fields would make them infinitely more useful, approaching true user-defined fields.

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: Searching for a specific row in a table field

Hi Tom,

Would editing the query to include a condition that rownum=0 not work? Do you need to find the value in any column of the first row or one specific column?

[See disclaimers below, the following is not a tested solution]

You might try something like:

select all
from ecatalogue
where (<nested grid fieldname> = <query condition>) AND (rownum = 0)

Here is a partial query that Jay K. provided Penn during the design phase to describe how to join columns of a nested grid by row number. This is not a solution to your problem, but might help to inform one.

and exists
(
select all
from
(
select rownum as r1
from COLA_tab
where COLA contains 'TERMA'
),
(
select rownum as r2
from COLB_tab
where COLB contains 'TERMB'
)
where r1 = r2
)

Disclaimer 1: I do not have time to test this tonight as I am starting a new project.

Disclaimer 2: I reviewed this post fairly quickly, so please forgive me if I have overlooked anything.

All of my best to you. Hope to see you again soon.

Will

Will Scott
Museum & Database Consulting
www.willscottconsulting.com

Will Scott (Museum & Database Consultant)
Museum & Database Consultant
useravatar
Offline
37 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Searching for a specific row in a table field

Heather,

In case you have not already found a solution, write to KE support and ask them to provide instructions on joining by row number in nested grids. If that sentence does not make any sense, just copy and paste all or part of this message in your support request. I believe that there is a way, not a user-friendly one, but a way to accomplish some of the searches you described in your 10/13/2013 post using Show Search. It will require editing the query TexQL, but should work without needing to know the precise row number.

I share the frustration with KE not communicating query limitations for these fields during design. I was lucky in my last project to have Jay K. raise it as a concern early on, but it seems that other clients might not have been given a "heads up." The tabular appearance of these fields suggest relationships that do not exist. New clients, selecting field types as part of designing an EMu configuration, will naturally assume certain query functionality. Perhaps that could be addressed as part of standard initial training (prior to client template selection).

Best,

Will

Edited by: Will Scott (Museum & Database Consultant) - 08-May-14 14:29:23

Will Scott
Museum & Database Consulting
www.willscottconsulting.com

Will Scott (Museum & Database Consultant)
Museum & Database Consultant
useravatar
Offline
37 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Searching for a specific row in a table field

Thanks, Will, for this suggestion - we'll play with it.  We did ask KE support for a work-around, and did get some query language that produced a read-only list of records, which we still had to copy and paste into Excel, then paste back into a search window in EMu to retrieve the records. 

You put it well - "the tabular appearance of these fields suggest relationships that do not exist."  I asked this question at the last User Group Meeting, and it didn't sound like this was impossible to fix, but it hadn't been a priority.  Tom raised this issue a long time ago, and I feel like it's time for KE to at least give us some concrete feedback on the problem.

Heather Thorwald
Registrar
useravatar
Offline
6 Posts
Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Searching for a specific row in a table field

Hi all,

There is a similar call in User Forum that contains some example Texql using either:

an explicit Table/Row reference  CatOtherNumbersSource_tab{2} or
a count statement  count (CatOtherNumbersSource_tab) > 1   

to find data in tables. 

The basic process is:

1. Create a search (don't need to run it)
2. Go to File > Show Search  (to open the TexQL editor)
3. Edit the Texql to look for the fields you need.

You should note that some texql operators - like count() - puts EMu into Read-Only mode when it runs the query. If you need to work around this, save the record set as a group then view the group to edit the records.

Regards,
Deb Cady
Axiell Sydney

Searching for records with more than one entry

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

Board Info

Board Stats
 
Total Topics:
601
Total Polls:
0
Total Posts:
1362
User Info
 
Total Users:
827
Newest User:
Marcus Swann (Axiell Melbourne)
Members Online:
0
Guests Online:
212

Online: 
There are no members online