FIND multiple

[johnnyrocknyc]

Hello All.

Does anyone know an easy way to find a large number of records? I would like to copy and paste about 500 values from an Excel/Text file and search one field in my DB. To do this individually would take hours.

I'm not so adept in Scriptmaker, and imagine this would be one way of doing it. If anyone has suggestions, it would be greatly appreciated.

For clarification I'd like to search one field for something like A11, and C22, and F51, and...etc.

Thanks,

John

[QuinTech]

Hi Johnny. Try using a relationship instead of a find. The fact that you can use a multi-keyed relationship should be useful. For example, if you have one field whose contents are:

A11

C22

F51

...

And you relate that field to another where those values might actually appear, then each of the records that contains a value (A11, C22, F51, ... ) in the appropriate field will be matched to that. If you then display a portal on that relationship, you will get something functionally equivalent to a found set. Then you can just paste your values into the field on the left-hand side of the relationship, making sure there is a carriage return after every discrete value.

HTH,

Jerry

[transpower]

Not clear. Are you pairing two values, the field label (A11, C22, F51) and the corresponding value? This looks to me like a job for a matrix, using the custom matrix functions.

[QuinTech]

No. A11, C22, F51 appear to me to be sample values rather than field labels. If you have one field named SearchField whose contents are:

A11

C22

F51

and another field named EntryField, in which each of the values above is unique in one record (that is, three different records and each one has a different value in EntryField); in that case, all three records will be returned by a relationship from SearchField to EntryField. Thus a portal using that relationship will simulate a find.

What is a custom matrix function?

J

[transpower]

Your interpretation of the post may very well be right.

My guess is that the poster would like to import an Excel file into one field in one record rather than one row per record and and one column per field. And, further, he would like to find the value in element A11 and C22, etc. If this is the case, then using The Shadow's custom matrix functions would work, although this would require Developer. A field can be set to an identity matrix, by Identity(n), where n is the number of rows or columns. Then using my SetCell custom function inside of a another recursive function (which would have to be created) one would copy in all fields of a record into one row of the matrix, and repeat for all rows. So you would end up with a matrix in one field on one record. Then to retrieve a value from the matrix, one just uses the GetCell custom matrix function; for instance if the matrix is name MatExcel and if you want the equivalent of Excel's A11 element then the command would be GetCell(MatExcel;1;11).

Or maybe the poster just needs to learn to use Table View!

Or, more probably, my interpretation of the poster is just plain wrong.

[johnnyrocknyc]

Thanks so much for the posts. I think I should clarify, though.

Consider a DB with 100,000 dog names in it. The names are stored in a field called NAME.

I have a list of 500 names that I want to find in the database to create a subset. So, I can go to find mode, click in the NAME field and type "Fido", then perform a new find request and type "Rex", etc. I do this 500 times with the 500 names, and I'll get my subset which I can then flag.

Is there a way that I can submit all of my requests at once? I'm not sure if there's a separator that I can use like a comma or 'AND', but I'd like to be able to find Fido and Rex and Tiger and Astro and Dino, etc...

-Thanks again, and pardon me if this is a stupid question.

[-Queue-]

You can use a global text field and a relationship from it to the NAME field. Then you can enter the names separated by

[Fenton]

I think you should use a relationship. Create a Text field, global storage, _gDogNames.

Create a Table Occurrence (TO), "Dogs__gDogs," same Dogs base table.

The relationship is from global _gDogNames in the main Dogs table to the DogName field in Dogs__gDogs TO (not the other way around; you go from the global to the regular field).

Be on a Dogs table layout

Copy the dog names, paste into the global field.

Commit Record (click outside the field anyway)

Go To Related Record [ Show, "_gDogNames" ] <--Attach to button

This is going to be "exact" matches, any of the pasted names to any of the Dog names.

[transpower]

Hmm...your second post is clearer than your first, and my interpretation of the problem is completely different from what I had before. So follow -Queue- and Fenton, or (possibly) you could write a script to create the 500 requests (after entering Find mode in the script) based on setting the name field equal to a repetition in a repeating field containing all your OR'd requested names and looping.

[transpower]

I'm still thinking about this problem. A matrix for use with the custom matrix functions is a text field all of whose elements have an added semicolon. So if we have a text field with 500 lines for the 500 names, all of them having a semicolon, then we have a 500 x 1 matrix. So, in a find script, one could loop through 500 new requests and use Set Field Name = GetCell(matrix;N;1), where N is the current request number (a global).

A matrix would be better than a repeating field here, because a repeating field cannot have a scroll bar.

But one could go still deeper into this problem. Is there any other field that unites the 500 dog names? If so, then one would simply use that field to make the selection. It's difficult to believe that the 500 names would be completely unrelated.

[MoonShadow]

Good grief ... go the relationship route; listen to Queue and Fenton. It is simply the best choice here.

It will provide more flexibility for change and growth; you can filter portal displays in ways you haven't even thought of yet (but will); you can take advantage of GTRR and you will be set up for future TOs based upon other criteria.

Don't create 500 Find Requests or paste your field and then have to add semi-colons or use repeating fields and loops ... one relationship can do it all for you.

[johnnyrocknyc]

Thanks so much. I'm going to try the relationship approach. I'll let you know how it goes.

I really appreciate all of your posts.

-John

[Bailey Kessing]

I use the relationship route all the time to find large lists of values form a text file or excel...very easy. The multi-key relationships are great for this!

[johnnyrocknyc]

I have been so busy using this new technique - I forgot to respond with my most sincere thanks. It works like a charm. It has saved so much time. Thanks again.

-John