5 Replies Latest reply on Mar 30, 2016 5:41 PM by happyez

    Find Records on Multiple Critieria (marked as question this time...:)


      (I had this previously, but I forgot to mark as a question, so I reposted it. Hopefully that's not a problem! if it is, I'll learn. New at these things)


      Hello everyone


      Here is a bit of a doozy, but one that might be simple enough if you use dynamic filtering within portals and/or filtering within the relationship graph.


      My situation is similar to a travel agent - best analogy:

      People want to know which airlines to pick from, that show:

      • current location (NY) to

      • destination (London)

      • wanting an aisle or window seat

      • will accept specific airlines (Whoopsie Airlines or Antartica Express)

      • at a couple specific times (6am, 4:30pm, 11pm)

      Every option must taken into account together, and not one variable can be left out.

      This means that the db/server of the travel agent is doing, I guess in one way, loops of every combination to get a good set.

      Then they will sort via best price or preferred airline, and show the list (sort decreased) on the website.


      I hope that gives you the picture.


      Mine is similar. I am finding sponsors for events. If I could name it as a heirarchy, it is that I must match, as a basis:

      i) the client's sector (if they are a mental health non-profit, then their sector is 'Health' and 'Non-Profit or Associations') and

      ii) the sponsor's sector (they are after banks and telecomms, then the sectors they are after are 'Financial' and 'Telecom')

      Just on this, I need 4 find queries (Health + Financial; Health + Telecom; NFP + Financial; NFP + Telecom)


      This will then look through my 100,000 records for matches. I have both in portals (separate tables), and I want to find any sponsor that is a Financial sponsor and a Telecomms sponsor, and see which ones sponsored events that were NFP and were to do with Health. OK, that's simple enough.


      But I also need to know:

      iii) which cities the events happened in (the client may be wanting to have the sponsors from the above found set to also have sponsored events in NY, LA and SF (more cities in my case than that))

      iv) if the types of events were consumer events (eg festivals) or professional/trade conferences

      v) and whether they want to go after lifestyle brands


      so we must get a set of results that are i + ii + iii + iv + v, and this could be up to 1800 results easily or more.


      One way is to loop this like an odometer (01 02 03 ... 09 10 11) and get all combinations based on every combination being tested on a loop, but that takes 40 minutes to do.

      The other way may be filtering results, so the 100,000 record that must be poured through are first reduced to 20,000, then less and less, so in the end, it only takes, what, a few minutes to do.


      v), for example, is just one value within one field. If this is ticked, include this sponsor in with the set.


      I dont know how to filter the portals so it shows only certain results, what you are creaming off and what you are left with.


      So I was wondering what others think could be a solution.

      The reason I gave the travel agency is that, someone has done a FM script for them or even real estate agents (we want apartments and houses, but not land; we want in X Y and Z suburbs; there must be 2 bathrooms; 1 or 2 garages; and it must be renovated).

      So, they have probably done the hard work, or if they havent, someone comes up with it, and FM developers can enter the market of scripting for new markets!


      Anyway, I would love to know what people think. I am a newbie, but I also have someone I can share your ideas with, and he will be able to suggest something. Then we all share in the fruits....

      Or if it has been answered in the forums, someone could share the link (I couldnt find anything, incase anyone says "took me 5 seconds to find it!!)).


      Thanks everyone


        • 1. Re: Find Records on Multiple Critieria (marked as question this time...:)

          First of all, if you have 100,000 records, I'd avoid portal filters like the plague. It will be so slow as to be unusable, especially with a set of filter criteria this complex.


          What I would do here is use a combination of Extend and Constrain to perform a series of Find requests that aren't so complex. This will perform fine. I would suggest using a "find criteria" screen and allow the user to enter the selections, then run a script to execute.




          • 2. Re: Find Records on Multiple Critieria (marked as question this time...:)

            Hi Mike


            Thanks for that. Any idea what you would extend and constrain? Is there an order to it (similar to what I thought would be the first steps (comparing sectors))?




            • 3. Re: Find Records on Multiple Critieria (marked as question this time...:)

              Yes, there's an order to it. Extend makes the found set larger. Constrain makes it smaller.


              It's not entirely clear from your description how your various factors interact. But I would break it into manageable chunks and work from there. I would think of it in terms of "AND" and "OR" criteria.


              For example, you mention the original two criteria (client's sector and sponsor's sector). You could either use a set of 4 Find requests, like you have, or you could just use 1 request that has the client's sector, perform the Find, and then perform a Constrain on that set equal to the sponsor's sector. This forms a logical "AND" because I've first found one criterion, then I've narrowed it down to those records that are in that set AND that meet the second set of criteria. In this way, you avoid having to work through all the possible combinations.


              If, on the other hand, you need a logical "OR", you can accomplish this with Extend. So I could put in client's sector = "Health", then extend to sponsor's sector = "Financial". This will return the set of records where the client is in Health OR where the sponsor is in Financial.


              Hope that makes sense. I'm sure you can work it through once you get the concept.



              • 4. Re: Find Records on Multiple Critieria (marked as question this time...:)

                The order does matter. You should start from broadest set to most narrow set to make sure you do not miss anything.


                The real estate example is not exactly the same because there you have one basic record that has a number of single details related to it and for the sake of argument they could all me in one table without any relationship at all. Simply find only Land. Then constrain to include only X Y Z. Then constrain to include only where bedroom equal 2. Then constrain to garage is "1...2", and the constrain to only renovated. Fairly simple even on 100,000 records. I think we might call it pretty equal if you wanted houses and apartments but not land.


                There seems to be some confusion of the following things:

                iii. You are using a found set of past instances where certain client types were connected with certain sponsor types to see what sites that combination occurred over some known time frame, Ok. You want to use that found set of sponsors to be essentially a value list for potential future event?

                v. Who is trying to go after the brands? Clients or Sponsors? Are the Sponsors a brand?


                Ignoring the issue about the brands, I am guessing you have an Event Join table with relationships to Location, Sponsor, Client. Sponsor has related data about Types as does Client(2 types).


                The ideas that comes to mind is pull all the data into fields in Events via lookups and have a go at using slightly complex ExecuteSQL to get the distinct sponsor IDs and create a virtual list. If you need a found set loop through the IDs extending the set each time. You could loop through finds as well. It is about the same.


                More seasoned developers may have a better idea. Complex finds in FM can get....complex.

                • 5. Re: Find Records on Multiple Critieria (marked as question this time...:)

                  Hello everyone


                  Thanks heaps for your answers. I found the constraining and extending took a lot less than 45 minutes to do the script, but still involved some processing, though I managed to get it to around two minutes on one go.

                  I think I forgot to add that each of the find values come from different tables, four of them.


                  Eric Brown, who is on this forum (dont know his handle), showed me a way to do it which pretty much crunched it all in about two seconds, and it worked very well.


                  In my case with four different tables (sub::i, ii, iii, iv), you make a duplicate copy of each of these fields into the main table which all of these filter through (mainA). That is I have a layout (main1 layout) which shows me the data from each of these four tables. Main1 was duplicated in the relationships graph (MainA 2). Then I set up four text fields which had a calculated value of the fields within each of those disparate tables (MainA 2::i, ii, iii, iv). So I now have (sub i <=> MainA 2::i)


                  Now, with each of these four new fields, I create a new relationship occurance (?) within a relationship, and I link each of these new fields with the same version in another table (Client::i, ii, iii, iv) as a WD layout that a client will enter their data into.


                  So, I now link (Client::i <=> MainA 2::i) and so on down to (iv). They all equal each other.


                  So I am dynamically filtering. If the (Client::ii) field, for example, is a City field, the client will enter "NY" into it. If "NY" is found in (MainA 2::ii), then the portal will only show "NY". Since I am filtering four fields, then the client will be entering information into each. So it gets knocked down from 120,000 to 60 in 2 or 3 seconds!


                  He had to lead me through it because I didn't get it until the end. Also, this may be a crap way of describing what happened, so everyone will probably have a tonne of questions.


                  This is my first time showing something to the community, instead of asking questions, so I have finally made a contribution!!!


                  Thanks all, my education in FM has been much greater thanks to the community.