Jump to content
bbulmer

Find from list of values

Recommended Posts

bbulmer

Hi,

I was wondering if anyone can assist me with creating some way, most likely a script, to perform a find of multiple records from an external source of data. To be exact, I would like to find all records that match serial numbers that are listed in a text file. The text file can be any format as well, line break, comma, tab separated. I'd just like to be able to do a multiple find without having to paste in each new find request.

Thanks!smiley-smile

Share this post


Link to post
Share on other sites
Ender

If you could you import the text file into an existing FileMaker table (where each serial number is a separate record), it would be a simple matter to use those in a relationship to isolate those from your existing data set that match.

 

The best method for this depends on the version you're using. With FM7, you can use a scripted loop (or Copy All Records, or ValueListItems() calc) to put the serial numbers into a single return-delimited global text field. Then a Go to Related Records [show only related] on the relationship between the global and the Serial Number in the existing data set should produce the matching set.

Share this post


Link to post
Share on other sites
bbulmer

I'm the owner of the database I am trying to perform these functions on, so anything is game. I'm just trying to make thing easier for people atempting to use the databases, otherwise it gives them more leverage to move towards the dark side, known as ACCESS.

Any help is greatly appreciated.

Share this post


Link to post
Share on other sites
Ender

Making a database easy to use should be the goal whether it's built for others or just yourself. And that's the same in Access.

 

We don't know anything about your structure or what specifically you'd need help with, but if you have something in mind and can fill in the blanks, I'm sure someone can lend a hand.

Share this post


Link to post
Share on other sites
bbulmer

Lets reduce this to the lowest denominator.

I have a database with two text fields. One named "serial number", and another named 'Name".

I'd like to take the following text list of serial numbers:

 

abc1

abc2

abc3

abc4

 

And somehow perform a find in the database of these records to show these records only, out of the thousands of records in the database.

The way I would normally do this is: Find, (Paste in 'abc1'), new request, (Paste in 'abc2').......

Then the end result are all the records found from my multiple requests with the Name that matches the serial number listed.

 

Straightforward as it gets.

Share this post


Link to post
Share on other sites
Maarten Witberg

You could make use of relational possibilities here. Treat the list of IDs as a multikey field, relate this field to the table you want to view data from.

Then either perform a go to related records script step to take you to that table and layout, or view the results directly in a portal.

 

from the first post I understand that the serials you want to search on come in any kind of format so you' have to preprocess them to make them behave like a multikey. This requires the use of ¶-separated lists.

You can use the Substitute function to preprocess. How you do this depends on how you get the list of serials in the first place.

 

(edited)

Share this post


Link to post
Share on other sites
bbulmer

Heh, you're making me want to switch down to Novice level for my profile.Opps!

I don't really understand what you mean by "Treat the list of IDs as a multikey field". Nor do I know what you mean by viewing the results in a portal.

The field I'm searching in is nothing more than alphanumeric text. As stated, serial/name matchup search is exactly what I'm trying to do.

 

I get the list of serials from a report from another application, just plain line break text serials.

 

I think you're making this seem more difficult than it really is, I really thought this would be a simple script. Thanks for the response though.

Share this post


Link to post
Share on other sites
Maarten Witberg
I really thought this would be a simple script

 

yes it is. I can talk you through it, but I am assuming that you know how to set up a relationship and how to create a portal, if not check the Help Viewer.

 

0. setting it up

- Create a field called QueryField in the table that you wish to perform the search from. It is a text field.

- Create a relationship from the QueryField to the SerialNumber field in the table that holds SerialNumber and Name.

now you can do two things.

 

1. do a scripted find.

paste the line-break separated Serial numbers from the other app in QueryField

Create a one-line script

 

Go To Related Records [YourTable::SerialNumber #show only related records]

 

The script should present you with a found set of all records that have a serial number that's in QueryField. I there are no related records, you stay in the original layout.

 

2. do a find by portal

Create a portal in the layout that has QueryField, set up the portal so that it shows records from the relationship you've just defined.

If you paste any number of serials, the portal should, upon record commit, display all related records that match a serial in the QueryField.

That is what I mean by viewing the results in a portal.

 

multikeys...

Most relationships in filemaker are created by matching ID numbers, some call it primary key (=the auto-generated ID or serial that uniquely identifies a recrod) to foreign key (=a field in a second table that can hold a serial number that matches a record in the first table.

Filemaker has the ability to match any string in a text field that is separated by a "¶" to any record in the second table. This is called a multikey.

You can make use of this to create a many-to-one relationship.Your lists of return-separated serials perfectly match this format, so that's why I suggested you make use of this feature.

 

A "normal" find procedure would require you to create a script that loops through the list of serials, perform a find on each serial and expand the found set according. I suspect that it's slower for a long list of serials; it's certainly a more difficult script to create.

 

I hope this clears up matters for you.

 

switch down to Novice level

nothing wrong with that. I decided a while ago that there are only two levels of skill for Filemaker: one is you know all there is to know, the other is you don't. So far every time I start up filemaker I learn something new. yeah yeah, very deep. Seriously, this skill indication doesn't mean much. No one set a standard for intermediate or novice or developer level and no-one checks if you really live up to it.smiley_cool

Share this post


Link to post
Share on other sites
Ender
Seriously, this skill indication doesn't mean much.

I'll respectfully disagree with you, kjoe. The skill level is helpful in communicating on an appropriate level, though understandably it can be hard to judge one's own skill.

 

I honestly don't recall putting "Developer" as my skill. Maybe we should have a "Know-it-all" category for humble folk like ourselves. ;)

Share this post


Link to post
Share on other sites
Maarten Witberg
I honestly don't recall putting "Developer" as my skill.

 

see? it's a title you get given, like a knighthood. :)

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use