Looping Find

[Ron Cates]

Hi All,

I am trying to perform a find for a report based on a field called status. I start with a checkbox set with several available statuses. The user can select any number of statuses to base the report on. So I am trying to figure out how to script a find based on a field with return seperated values. I suspect a loop is in order but I'm not sure how to count through the values in order to go from one to the next and exit after last.

Thanks in advance B)

[Ron Cates]

Never mind. Found my answer here http://fmforums.com/forum/showtopic.php?tid/175649/

[David Jondreau]

There's a few ways to run a loop. One is with a $counter. The other is by removing values from your list:

Set Variable[$values; Table::Status]

#

If[not IsEmpty($values)]

#

Enter Find Mode[]

#

Loop

Set Field[table::SearchField; GetValue($values;1)

Set Variable[$values; RightValues($values;ValueCount($values)-1)

Exit Loop If[isEmpty($values)]

New Record/Request

End Loop

#

Perform Find

#

End If

Edited September 14, 2010 by Guest
ninja'd by comment's better solution and he's not even in the thread!

[Ron Cates]

Well i'm about to make this a bit more complicated. The solution I used from the thread I posted looks like this:

Enter Find Mode [ ]

If [ ValueCount ( Administration::xreport_status ) ]

Loop

Set Field [ Jobs::year; Administration::xreport_year ]

[color:red]Set Field [ Tickets:_fk_sales_person_id; Administration::xreport_sales ]

Set Field [ Tickets::status; MiddleValues ( Administration::xreport_status ; Get ( RecordID ) ; 1 ) ]

Exit Loop If [ Get ( RequestCount ) ≥ ValueCount ( Administration::xreport_status ) ]

New Record/Request

End Loop

Perform Find [ ]

End If

As you can see there are other criteria involved. The set field marked in red is another field I would like to provide a checkbox set for with multiple options. I suspect I will have to nest another loop in order to accomplish this. Not sure how to do it?

[comment]

I think this may be a good candidate for using Constrain Found Set[].

BTW, from across the years: it would be better to use Get (RecordNumber) instead of Get (RecordID). Although Get (RecordID) does work, it's not strictly right.

[David Jondreau]

First off, have you tried it yet without this new addition? It looks like you making a newbie error of trying to grab values from a field when in Find mode.

Unless Administration::xreport_sales is a global field, you need to load it into a variable before entering Find Mode.

How to add your new criteria depends on whether this is an "AND" or an "OR" find and what the data actually represents. Do you want to find the Tickets that are Active and for Joe Smith or the Tickets that are either Active or for Joe Smith? Is Administration::xreport_sales a multiple value check box list?

[Ron Cates]

Comment: Per your advice, I switched to Get (RecordNumber). Could you elaborate on how you would use Constrain Found Set[] in this case?

DJ: Administration::xreport_sales is a global. I would like to make it a checkbox set just like status. The technique has been implimented and tested successfuly.

The goal is to find all statuses selected AND all sales peaople selected. If a status or sales person are not selected I do not want them included in the report.

By the way, thnks to both of you for you input thus far B)

Edited September 14, 2010 by Guest

[comment]

Roughly:

Enter Find Mode [ ]

Loop

Set Field [ Jobs::year; Administration::xreport_year ]

Set Field [ Tickets::status; GetValue ( Administration::xreport_status ; Get ( RecordNumber ) ) ]

Exit Loop If [ Get ( RequestCount ) ≥ ValueCount ( Administration::xreport_status ) ]

New Record/Request

End Loop

Perform Find [ ]

#

Enter Find Mode [ ]

Loop

Set Field [ Tickets:_fk_sales_person_id; GetValue ( Administration::xreport_sales ; Get ( RecordNumber ) ) ]

Exit Loop If [ Get ( RequestCount ) ≥ ValueCount ( Administration::xreport_sales ) ]

New Record/Request

End Loop

Constrain Found Set [ ]

[Ron Cates]

Beautiful!! Thank you again B)