Tunneling and portals

[George]

I have 3 files as follows: Header to Items; Items to Invtry. For tunneling I have created 2 calculated fields c_WT and c_Dept in Items. The 2 fields are in Invtry.

My header form has a portal of Items fields plus these 2 calculated fields. When I perform a find on the Header form, one of my records that matches my find criteria may be on row 3 of the portal but the report only displays the item and amount of the incorrect record on row 1 of the portal, causing my report to be incorrect.

How can I have the report use the correct record in the report?

Thanks

[CobaltSky]

Hi George,

Relationships return the first related record, except when referenced via the Last( ) function.

However there are a couple of different methods that can be used to address (and retrieve) data from a specific related record. In essence, they fall into two categories.

The first approach (and variations of it) use the relationship sorting principle. For instance, you could hard code a sort key that will return the record(s) you are looking for as the first related record. Alternatively, you could set up a dynamic sorting system (based on sorting of a calc field that is driven by user determined variables) so that you can determine the sort on-the-fly.

The second approach is one which utilises a second relationship to narrow the criteria so that only one record is returned. This is typically done by setting up a global field to hold the left key of the relationship, and having it target a unique key field in the related file (eg a recordID or a concatenated string. When the first relationship locates a group of records, the user must then select one from the group (and on selection, the unique key value of that record is placed into the global field, thus ensuring that the identified record is returned by the second relationship.

You'd need to say a little more about the criteria by which the correct record for display on your report would be identified, before it would be possible to see which method might be most suitable.

[George]

Thanks Ray,

My specifics would be:

In the Header I query for Salesman: Ray, Invoice date >= 1/1/2002 next I have added for query purposes, the 2 calc fields c_Dept and c_WT, which tunnel to 2 fields in Invtry, in the portal.

Pseudo code is this: Since 1/1/2002, how many items of this weight, in this department has Ray sold.

[CobaltSky]

Ok, but which row of the portal do you want to see. Is the information you want always on the last row, or are you instead trying to summarise the data from all rows?

[George]

Since this is an invoicing system, it will always vary. As you can see from the image, if I query for Dept "ER" and WT >= .95 my report should include the 2 items in row 3 and 4.

[CobaltSky]

Since you are really wanting to be able to return a subset of the items, rather than to isolate a single portal row, what we are talking about is portal filtering.

The basic technique for filtering is a little like what I described above concerning establishing a second relationship. The key field in the related file must be a calculated field that concatenates (eg separated by a space) all the field values that you will want to be able to filter on.

You will then need a method by which your criteria can be converted to a series of values on separate lines, which can be placed into the global field to establish the relationship. There are several ways of doing this - and it can get quite complex if you want to provide scope for a lot of criteria.

There are three ways that I suggest you consider:

1. Create a script which takes your criteria (from global fields where you enter them) and works through nested loops to generate all the possible combinations - placing the resulting compound formulation into the global key field.

2. Establish a calculation (eg incorporating Mikhail Edoshin's 'Smart Ranges' technique) which generates appropriate matching keys in both files.

3. Use one or more plug-ins such as the portal filtering plug-in from Waves In Motion , and/or the ranges plug-ins from Troi Automatisering and Protolight.

There are advantages and disadvantages of each of the above. Perhaps the most flexible would be the last, but it will require that you but a shareware license - and tackle some external function scripting. The previous methods will be free and purely FileMaker native, but will present a few challenges, especially if the criteria you want to support are very broad.

For online details about the latter two suggested techniques, take a look at:

Onega Software - Smart Ranges technique:

http://www.onegasoft.com/tools/smartranges/

Waves in Motion - Portal Filter plug-in:

http://wmotion.com/portalfilter.html

Troi Automatisering - Ranges Plug-in:

http://www.troi.com/software/rangesplugin.html

Protolight - Freeware Ranges Plug-in:

http://www.protolight.com/plPLUGs.html

Two of the above are freely available, the other two are shareware, but if you're interested, all four provide downloadable info and demos etc.

There will be a bit of work in getting a filtering system operating - whichever method you choose - but the end results will pay off, I guess.