Display results based on two other tables

[Tyra]

Been away from FM for about a year working on other projects, and now I seem to have a brain blockage..

 

I have the following:

 

1. Ticket Table 

UID

status (Open, Close, Escalated)

 

2.Issue Table

UID

country (US, Canada, Brazil, Italy, etc..)

 

3. Search Table

UID

country

status

 

Ticket table is joined by UID to Issue Table.

 

Need to allow the user to use the Search table by selecting a Status and adding more then one country (sometimes 130 countries) and display a list of all UID's that match both those criteria. I can make it work with no problem using one criteria, but getting it to constrain to the status selection is causing me an issue. I know haing it all in one table would make it work much easier, but, this is just a simplistic breakdown of what I am trying to do, and there are hundreds of countries in the records but only a few tickets actually open at any one time. The databases currently have over 500k records, so speed is an issue also. Any help would be most appcreiated! A year ago, I could have probably figured it out on my own.. :-(    

[GisMo]

So Ticket and Issue is a one-to-one relationship? Assuming that's the case, I would create a list layout based on tickets table and create two global search fields one for status and one for country. Actually, the search fields could be a different form view layout. Country can be a check box set and the user can select any number of countries. Create a script that will loop through the country search list field and create a find for each country and status. I would probably use extend found set versus entering find mode and creating numerous find records.

Your find queries would be something like this within the loop

Ticket.status=$status

Ticket>Issue.country=$country

I hope this wasn't too brief, but let me know if you need clarification on the loop through the checkbox items.

[Matthew F]

I don't understand why there is a separate Search table.  Why not just have a special Search layout that is based on the Ticket table?  I guess having a separate Search table may slow down your performance since your search criteria are going to be based on relationships and not indexable. 

 

There shouldn't be a problem finding the records that meet two criteria.  For best performance, create a script that searches for the indexed criteria first, and then uses the "Constrain Found Set" script step for the non-indexed fields.  You can put the search criteria into global fields on the search layout and then have your script use them to set the UID and Status fields during the Find steps... No need for a separate Search table. 

 

P.S. If you take this approach, then you should turn on Error Capture and have your script navigate back to the other layout or return to Find mode if records are not found.  You don't what your script to halt in browse mode on the Search layout because the user might inadvertently delete unseen records.  (Maybe that's why you made a Search table?)

[Tyra]

Thanks for both inputs. The reason that I am using a separate table is that there are 3 work shifts, and based on time zones, each shift gets assigned their set of counties to work on trouble tickets. So, although the countries may change some (They need the ability to ad/subtract), they for the most part stay the same (i.e... shift 1 has 129 countries, Shift 2 has 75, Shift 3 has 50). So, I can't use a global field or a field in either databases, since it would be record specific.

 

I ran a speed test, and it actually was pretty quick in with the following setup:

 

GTRR from Seach table (countries) to Issue table (countries).

Added the (status) field from the Ticket table to the Issue table.

Preform a find for all "Open" (ticket:status).

 

My inital goal was to limit the records to only the "Open" ones first to cut down the amout of records that the (countries) list was being preformed agaisnt, but the join relationship actually returned them much faster then I had expected.  

 

I then just loop through the records and grab the UID's and add them to a global field for display.