Filtering List with Script and Find Function

[laguna92651]

I am new to scripting and looking for a little help.

 

I have a set of radio buttons in the header of a Table. The fields are global, I would like to filter a list with the following selections.

 

   Active, Inactive or All

 

I can do the Acitve and Inactive, but what should I add to the script to see all records. I am using, for just Active and Inactive only selections the following script. The xStatus is linked in the Global table.

 

   Enter Find More[]

   Set Field [Products::Status;Global::xStatus]

   Perform Find[]

 

I assume I need an If or Case statement somewhere?

 

Related question, what would the script look like if it were an AND situation, Check boxes with the following.

 

   Customers, Suppliers, Prospects

 

thanks

[jbante]

Try something like this:

 

Set Variable [$status; Value:Get ( ScriptParameter )]

If [$status = "All"]

Show All Records

Else

Enter Find Mode []

Set Field [...]

Perform Find []

End If

 

You're list of "Customers, Suppliers, Prospects" reads like a list you'd want to find with OR criteria rather than AND criteria — we want records that match any one value in the resulting found set, not just records that match all values. Do I have that right? If I do have that right, you do an OR search by creating multiple requests in find mode:

 

Set Variable [$typeList; Value:Get ( ScriptParameter )]

Set Variable [$typeCount; Value:ValueCount ( $typeCount )]

If [$typeCount = 0]

Show All Records

Else

Enter Find Mode []

Loop

Set Variable [$i; Value:$i + 1]

Set Field [Table::type; GetValue ( $typeList ; $i )]

Exit Loop If [$i ≥ $typeCount]

New Record/Request

End Loop

Perform Find []

End If

[laguna92651]

Thank you the Active/Inactive/All script, worked perfectly.

 

In the second case, I would like to make multi-selections. In the case of Customers/Suppliers/Prospects, these are "hard wired" into a custom Value list, so could select one, two or all three of the types.

But I also a situation, where I have a value list that records could be added to (n number of records), I would like to pop that up that value list in the header of table or list view, mark the records (multi selections) that I want to filter the list for, then filter the list. I haven't tried your second script yet, does it do that, multi selections?

[jbante]

The second script should match on more than one selection, such as if the selection global field were formatted as a checkbox set.

[laguna92651]

The second script sounds like what I would like to do. How would I go about doing a script for  a potentially changing value list. In the example I would want to filter by White, Red and Pink.

 

Color Value List

Select    Color

    x         White

              Green

    x         Red

               Blue

    x         Pink

[jbante]

The second script doesn't care what values are in the value list, only what values are selected, so it should continue to work the same way when you modify the value list or use a different value list with the same field.

[laguna92651]

I've modified your suggestion some. This script currently shows all records that does not have "Labor" in the Materials field or shows records determined by the other status options, Active or Inactive, and no "Labor" entry in the materials field. I would like to add a global checkbox field, No Labor Included, that would allow me to see or not see records that have "Labor" in the Materials column. Basically I want to be be to turn the hard wired Omit on and off.

This script is working, is there a way to copy this from my script area and paste it into a text file, to save me some typing and mistakes?
 
Set Variable[$StatusGRID;Value:GlobalProduct::xStatusGrid]
if[$StatusGRID="All"]
    Enter Find Mode[]
    Set Field[Products3::Status"*"]
    New Record/Request
    Set Field[Material2::Fabric;"Labor"]
    Omit Record
    Perform Find[]
Else
    Enter Find Mode[]
    Set Field[Products3::Status;GlobalProduct::xStatusGRID]
    New Record/Request
    Set Field[Material2::Fabric;"Labor"]
    Omit Record
    Perform Find[]
End If

[jbante]

You could take a screenshot and post that? Would this do what you're looking for?

 

Set Variable [$StatusGRID; Value:GlobalProduct::xStatusGrid]

Set Variable [$omitLabor; Value:GlobalProduct::xNoLaborIncluded]

Enter Find Mode []

Set Field [Products3::Status; If ( $StatusGRID = "All" ; "*" ; /* Else */ $StatusGRID )]

If [$omitLabor]

New Record/Request

Set Field[Material2::Fabric;"Labor"]

Omit Record

End If

Perform Find []

[laguna92651]

Thanks, I got the last script to work. I'm trying to get this script to work and am having problems.

 

Set Variable [$typeList; Value:Get ( ScriptParameter )]

Set Variable [$typeCount; Value:ValueCount ( $typeCount )]

If [$typeCount = 0]

Show All Records

Else

Enter Find Mode []

Loop

Set Variable [$i; Value:$i + 1]

Set Field [Table::type; GetValue ( $typeList ; $i )]

Exit Loop If [$i ≥ $typeCount]

New Record/Request

End Loop

Perform Find []

End If

 

When I look at the data with the debugger I am always getting the $typeCount = 0, so it it always showing all records. Shouldn't I reference the value list I'm using somewhere it the script? What is the Set Variable [$typeList; Value:Get ( ScriptParameter )] doing?

Thanks

Al

[doughemi]

It appears that the script parameter being passed to the script contains the value list.  We need to find out what the script which calls this one is passing to this script.  It will look something like Perform Script ["theScript"; Parameter: SomeValue] in that script.

theScript is the name of the script you posted; SomeValue is either a literal list, a calculation such as List(SomeField), or a field that is a return-separated list.

 

That value is passed to the script and is then stored in the variable $typeList. 

 

Oh, by the way, it may just be a transcription error, but the second Set Variable step should be

 

Set Variable [$typeCount; Value:ValueCount ( $typeList )]

[laguna92651]

Structure of value list I want to filter for. Mark selects the specific records to filter for.

 

Style (list name)

Mark      Style

             Tank

    x       Shirt

    x       Hoodie

             Jacket

    x       Sweater

 

From a button I went to Perform Script, then Specify.

In the Optional script parameter: I selected Edit and selected the list "STYLE" and the field "Mark', STYLE::Mark, Mark is the field the determines which records I want to include in the find.

 

The code was modified as shown below: I still only get a $typeCount =0 in the data viewer.

 

Set Variable [$typeList; Value:Get ( ScriptParameter )]
Set Variable [$typeCount; Value:ValueCount ( $typeList )]
If [$typeCount = 0]
Show All Records
Else
Enter Find Mode []
Loop
  Set Variable [$i; Value:$i + 1]
  Set Field [sTYLE::Mark; GetValue ( $typeList ; $i )]
Exit Loop If [$i ≥ $typeCount]
New Record/Request
End Loop
Perform Find []
End If

Edited December 4, 2012 by Lee Smith
remove several returns

[doughemi]

See if this helps.

 

The checkbox field will automatically show any new styles added to the table.

styles.fmp12.zip

[laguna92651]

That is very slick how you did that. But it isn't quite what I'm looking for.

 

I want to have a list of records, main list (could be either List view or Table view) then in the header of the list I want to pop up a Style list and mark the list to choose the Styles I want to see in my main list. Either filter the main list when I close the Style list or press button on the header after I close the Style list to filter the main list.

 

Thanks

[doughemi]

I can't think of a way you can make a checkbox list pop up in a layout header (that could overlap the body).  You can, however, create a new window (a pop-up window) with the checkbox list in it, then use the technique I used to get your found set, and close the window.

styles.fmp12 2.zip

[laguna92651]

I like it, that was what I was looking for. How would that work if I had two tables.

 

Table 1, main list, is of form. This is the table I want to filter.

 

ProductCode      Color    Fabric    Style

 

Table 2 is the pop up list, as you show in Layout #2. It would be the table I would use to filter Table 1 In my app it is used as a Vaule list to build the main list.

Along with marking the style in the popup, I would need to be able to add to the pop up list over time, so it is dynamic.

 

 

Thanks so much.

Al

[doughemi]

Define a value list of the items in Table2, and assign that list to the checkbox field.  The field itself needs to remain in the table the layout is based on. As more records are added to Table2, the value list will grow automatically, and thus the checkbox display will, also.

[laguna92651]

Thank you. What do you mean the field needs to remain in the table the layout is based on?

[doughemi]

The checkbox field must still be in the original table (even though the values in it come from a value list based on the second table) and the layout in the pop-up window must also be based on the original table.

[laguna92651]

Thank you very much Doug and Jeremy for all of your help, after a little work on this end I was able to get things working in my app. Turns out to be a very nice and straight forward approach.

 

A couple of questions.

1. Why does the checkbox field in the Styles table need to be global?

2. If the parameter I pass is a checkbox field, a 1, why does it show up in the ScriptParameter as the selected Style?

3. Haven't tried it yet, will the pop-up work if I use list view instead of just form view with checkboxes?

4. How would I AND several different fields; e.g. Style AND Material AND Color.

5. Is there an option to completely remove the scroll bars on the pop-up find window, they are empty now but have the scroll tracks completely go away.

 

Thanks

[doughemi]

 

A couple of questions.

1. Why does the checkbox field in the Styles table need to be global?

2. If the parameter I pass is a checkbox field, a 1, why does it show up in the ScriptParameter as the selected Style?

3. Haven't tried it yet, will the pop-up work if I use list view instead of just form view with checkboxes?

4. How would I AND several different fields; e.g. Style AND Material AND Color.

5. Is there an option to completely remove the scroll bars on the pop-up find window, they are empty now but have the scroll tracks completely go away.

 

Thanks

1. So that the field settings can be seen regardless of which record may be currently selected.  If the checkbox field were in a related table, related to every record in the styles table, it would not have to be global.
2. The value of the checkbox field is not a 1; it is a return-separated list of the checked values from the value list. (to see this, put a temporary copy of the field on your layout formatted as an edit box.) Since the value list consists of a list of designs, that is what the field returns
3. No.  You cannot select multiple items from a list view.
4. Basically the same way. First you get the design, then select the available colors, and then the material. In subsequent scripts, you would use Constrain Found Set[] instead of Perform Find[].
5. You would have to adjust your layout size to be smaller than the window size.

 

[laguna92651]

Thanks Doug for your help and clarification, very useful insights.

Al