FM analog to: SELECT MAX(col) FROM tbl WHERE name like "%string%"?

[MacDigger]

What it the best way to implement analog of the following SQL query in FMP 5 (in a script):

FROM tbl WHERE name like "%string%"

All I can think of is to arrange a LOOP, but I'm afraid its not the best way to do that. Any better ideas?

Thanks in advance!

Mike

[Chuck]

quote:

---

Originally posted by MacDigger:

What it the best way to implement analog of the following SQL query in FMP 5 (in a script):

FROM tbl WHERE name like "%string%"

All I can think of is to arrange a LOOP, but I'm afraid its not the best way to do that. Any better ideas?

Thanks in advance!

Mike

---

I might be able to help out, but I don't know SQL. What does that SQL query do?

Chuck

[MacDigger]

quote:

---

Originally posted by Chuck:

I might be able to help out, but I don't know SQL. What does that SQL query do?

Chuck

---

Thanks for the response!

This query finds maximum values of all elements named "col" of all records which have element "name" containing text "string".

A simple example: a table with columns col (containing numbers), and name (containing strings). I want to filter out all records which contain "string" in their "name" elements, and get MAX value of "col" element from that filtered list.

Its probably a pretty long description, but somehow it's easier to write a query then explain what it supposed to do

Thanks in advance!

[Chuck]

quote:

---

Originally posted by MacDigger:

Thanks for the response!

This query finds maximum values of all elements named "col" of all records which have element "name" containing text "string".

A simple example: a table with columns col (containing numbers), and name (containing strings). I want to filter out all records which contain "string" in their "name" elements, and get MAX value of "col" element from that filtered list.

Its probably a pretty long description, but somehow it's easier to write a query then explain what it supposed to do

Thanks in advance!

---

OK, since I'm not really familiar with SQL, I'm going to try to restate what you want in FileMaker terms.

You want to find all the records that have a certain string in one field, and of those found records, find out what the maximum value is a number field.

I can think of a number of ways to do this, but the easiest I can come up with is to enter Find mode, type in the string you want to search for in the field you want to search on, perform the find, and then sort the records in descending order based on the number field. The first record in the found set after the sort will be the maximum value.

Does that answer your question?

Chuck

[MacDigger]

quote:

---

Originally posted by Chuck:

OK, since I'm not really familiar with SQL, I'm going to try to restate what you want in FileMaker terms.

You want to find all the records that have a certain string in one field, and of those found records, find out what the maximum value is a number field.

I can think of a number of ways to do this, but the easiest I can come up with is to enter Find mode, type in the string you want to search for in the field you want to search on, perform the find, and then sort the records in descending order based on the number field. The first record in the found set after the sort will be the maximum value.

Does that answer your question?

Chuck

---

Yes, that's exactly what I want!

The only exception is that I need to hide this operation from user, therefore make all finds/sorts, etc in a script.

Is there a better way to do that, then running a LOOP?

Thanks!

[Chuck]

quote:

---

Originally posted by MacDigger:

Yes, that's exactly what I want!

The only exception is that I need to hide this operation from user, therefore make all finds/sorts, etc in a script.

Is there a better way to do that, then running a LOOP?

Thanks!

---

Here's what you do to store the operation in a script that can be run from a button, from the script menu, or from another script.

Enter Find mode and enter your find criteria. You don't have to perform the find but you can if you want. All you have to do is enter find mode, enter the find criteria, and then go back to browse mode. Make sure that you don't perform any other find after you do this.

Choose Sort from the Records menu. Select the number field you want to sort on from the list on the left. Select the radio button at the bottom of the window for "Descending order" and then click the "Move" button.

Again, you can either sort now or simply click "Done".

Open ScriptMaker and enter the name of a new script. I'll use the name "Find and Sort" but your name might be more descriptive.

After you create the script you're given a script definition window with a default script. Click "Clear All" to get rid of those steps.

Scroll the list of script steps on the left until you see the step for "Perform Find". It's under the "Sort/Find/Print" subheading of script steps. Double-click it to move it into the script. Leave the option for "Restore find requests" checked.

In that same subheading of script steps, double-click the "Sort" script step. Leave the default options ("Restore sort order" and "Perform without dialog") selected.

Click "OK". If you want to run the script right now you can click "Perform". If you want the script to appear in the script menu, make sure that "Include in Menu" is checked. If you don't want it in the menu, make sure it is unchecked. Click "Done" to get out of ScriptMaker.

Now you can create a button to perform the operation for you. When the script runs, is will perform your find, perform your sort and leave the user in the first record, which will be the record with the maximum value you are looking for.

At this point you might want to do other operations. For instance, you might want to capture that maximum value by setting a global field to the value in the first record, taking the user to a special layout to report to them what that value is. What you do next depends on what you want to do with that value.

Chuck

[MacDigger]

Thanks alot!!

I'll try this approach.