Relationship based on SQL list

[eoneon123]

I'm not sure where to place this question as it seems to have overlapping content. But I'm trying to remember how to filter a relationship by way of a SQL list. I worked this out at the portal filtering level but it's too slow. But essentially I've created a conditional list based on selections made from four global fields on the display layout and table. The selection fields are not on equal footing to the extent that selecting "all" on one of the four fields might override the other three fields, subsequently setting them to "all" as well by way of a script...which is why I want to rely on a SQL list that I can control rather than just relying on the relationships.

 

So my question is how do I create this relationship? The SQL list is in a calc field on the display table and compiles a list of the unique Id's I want to display, but I don't think I can just have that field "=" the Id field on the other table. Any thoughts?

[dansmith65]

eoneon123, on 13 Feb 2013 - 13:50, said:

but I don't think I can just have that field "=" the Id field on the other table

Have you tried it? If I understand your explanation, then I think you can.

I also think you may have performance issues with this setup, but only testing will tell (be sure to test over WAN, if that's how the solution will be deployed).

[eoneon123]

The first time I tried it, it didn't work because I had some other problems in the relationship. Performance wise, it's quicker than filtering the portal but it's still seems slower than I like. I think I'm going to have to rethink this one. 

[dansmith65]

Other ideas you can try:

1. List view and perform find's based on global filter field's values.
2. Use global filter fields in relationships
3. Set id's to match field via script rather than field calculation - this would allow you to only evaluate this calculation when needed, not every time a filter field is modified.

[eoneon123]

Number three sounds like the best method if I understand it correctly--are you saying that I should just set a key field by script if my filtering conditions are met? I had that set up before but I didn't know if it was the best way to go. 

[dansmith65]

If I understand you correctly, then no, that's not what I mean.

Basically, take your current ExecuteSQL calculation and put it in a set field script step, attach that script to a button which is next to your filter fields. The assumption here is that you don't want the calculation to run every time a user modifies any filter field, you only want it to run when they are done selecting filter criteria.

[eoneon123]

Sorry for being so dense but let me flesh this out a bit more. I have two portals on a viewer (V-TABLE) layout accessing the same data table. I'm using two separate TO's of the data Table (P1) AND (P2) for the portals. I want to use P1 as a table of contents of sorts, while P2 is used for data entry--this is why list view is knocked out. I have an expand/collapse hierarchy set up on both P1 and P2 that operate independently as well as independent filtering set ups for each portal--which is why I'm using two separate table occurrences. 

 

For the TOC portal (P1) I have several global fields for filtering, which pertain to the original question in this thread. For (P2) there is only a single global field filter. The last wrinkle is that I have an expand/collapse button on each row of both portals where child records are filtered out. Now, I'm experimenting with the quickest, simplest way to execute all of this. I've gotten this to work in several ways but I have concerns about the speed. 

 

So far I've learned that: (1) having multiple portals on a layout is slower that a single, (2) portal filtering is slower than doing so by relationship, (3) multiple match keys is probably slower than a single key, and (4) the more calcs that need to be evaluated the longer it takes. I've looked at some of the other hierarchy/expand-collapse set ups but they seem to fall into two categories--they either depend on a cartesian join, which I think limits the creation of new records directly inside the portal (the point of P2) or they depend upon untangling elaborate custom functions (beyond my skill set at this point). 

 

At this point, the globals have script triggers that filter the portals, but I'm trying to work out something simple for the hierarchy buttons. I thought of having a match field on the VIEWER Table that was always set to 1, and then having a calc field on the data table which set to 1 if certain conditions were met. But the relationship doesn't work (because match fields can't be between a calc field and number field?). So far the quickest set up I can come up with is just doing the above but swapping out the calc field for a number field which is sql UPDATED by the hierarchy button. It's not terribly slow, but I always get the feeling I'm doing something wrong when I start adding fields to execute commands. Also, I've been experimenting with less than 50 records and I worry what will happen when the record count enters the hundreds or thousands.

 

I know this is a mouthful but I welcome any comments.