Need table occurrence to filter records

[hennaquick]

I have a table that has two interfaces. The first interface is a layout that should show all records. The other layout is to be based on a table occurrence that only shows records with a particular value in one field.

I have tried creating a separate field with that value then creating a relationship btwn the table and its occurrence based on the separate field and the field that may have the same value as the separate field. My expectations are that the table occurrence will only show the records that have the value I want in the specific field. However, I keep getting all the records and sometimes Cartesian results.

How should I relate the table occurrence to the table so that the interface(layout) I create can only see the records that have a particular value in one field? In what context, should the layout be? the fields?

Edited March 17, 2006 by Guest

[LaRetta]

Hi hennaquick,

Table occurrences will not show filtered values unless you 1) GTRR to the table occurrence (which will give you a found set of just those filtered items) or 2) create a portal (based upon this new table occurrence). You haven't provided much information to work with nor any table/field names so it is difficult to even describe but from the beginning:

You have a table (we'll call it MAIN). Create a global text field called gValue. This is where you will have a User put the value they wish to isolate. Create another table occurrence of MAIN called Values and join thus: Main::gValue = Values::fieldWithValue

There are two ways to view the filtered data: 1) GTRR (Go To Related Record) from layout based upon Main to layout based upon Values. This script step will place you on your 'filtered' layout with the found set of related values (after you enter a value in the global). GTRR would Go To Related, 'Get Related Record from Values' showing layout 'a layout created that is based upon values.' You will end up with a found set of records whose values match the global field.

The second method of display is via portal. Create a layout based up MAIN and then a portal based upon VALUES. Place the global on the layout and enter data. As you enter a value, the portal will filter and produce the results. You are correct that three things are important here: 1) The layout table occurrence (check Layouts > Layout Setup > Show Records From (yours should be based upon MAIN for the portal method). 2) The portal must then be based upon VALUES and 3) The fields within the portal should be based upon VALUES. Fields outside the portal should be based upon Main.

I hope this gives you enough to get started - let us know if you need further help.

LaRetta

Edited March 17, 2006 by Guest

[comment]

A layout is not a found set, and neither is a table occurrence. Any layout is capable of showing any or all records of the underlying table.

If you have a relationship that makes only some records related, you can quickly create a found set by using the Go to Related Record script step, with the option "Show only related records" checked. You GET the related records from the TO, but you can SHOW them in ANY LAYOUT based on the same TABLE (not just the same TO).

[hennaquick]

Ok..here's more detail.

The MAIN table is a table called [color:green]REQUESTS which has four choices for the [color:green]REQUESTS:[color:red]letter field. Controlled by a value list.

The choice I want to filter has the value [color:purple]"TO REPORTER" in the [color:green]REQUESTS:[color:red]letter field.

I created a global field called [color:brown]g_letter_reporter which I gave the value [color:purple]"TO REPORTER". I then created a [color:blue]TO named [color:blue]REQUESTS_REPORTERS and linked [color:green]REQUESTS:[color:brown]g_letter_reporter = [color:blue]REQUESTS_REPORTERS: [color:red]letter.

Now I want my layout to only show records that have [color:purple]"TO REPORTER" in the [color:red]letter field.

(Incidentally I also have a report that I want to do the same thing!)

How do you "script" a form/report to GTRR? Should I have clicked the option to create a script when creating this form/report? If so, how can I recapture those scripts if I didn't choose that option initially? Subsequently, how do I navigate to these form/reports using said scripts?

[LaRetta]

Hi hennaquick, this almost got past me...

Now I want my layout to only show records that have "TO REPORTER" in the letter field.

Place the global on your layout and attach your original Letter value list to it. Create a portal of REQUESTS_REPORTERS (and select the REQUESTS_REPORTERS fields to place within it). Select 'To Reporter' in the popup. Your portal should fill in.

For a report, I might ask ... why not just perform a find within the same table? Nonetheless, a script can be changed after it has been created and since you have the new relationship in place, I would use it (GTRR is quicker; particularly on large record-sets). Have button on Main layout to fire the script which might look something like this:

If [ IsEmpty( g_letter-reporter ) ]

Show Custom Dialog [ OK ; "You must choose a letter type. Please try again. :^)" ]

Halt Script

End If

Go To Related Record [ Show Only Related Records; From table "REQUESTS_REPORTERS"; Using Layout: "yourReport" (REQUESTS_REPORTERS) ]

You didn't describe your report. If it is columnar with Grouped data, you must include a sort on the break field (whatever you specified as Categories) so add the following steps.

Sort [ Restore ; no dialog ] ... Open Specify Sort Order to define your Grouped part sorts

Enter Preview Mode [ pause ]

Enter Browse Mode []

Go To Layout [ original layout ]

The best way is to create these scripts in a test file with a handful of records and watch what happens. The logic will become clear in no time.

LaRetta