2 Replies Latest reply on Mar 28, 2016 6:00 PM by happyez

    Find Records on Multiple Critieria

    happyez

      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

      Eric

        • 1. Re: Find Records on Multiple Critieria
          macwombat

          Hi Eric.  Its probably helpful if you don't worry about analogies with other industries, but just stick to your own problem you are trying to resolve, because your question is so long with lots of off topic stuff that it is hard to follow what your problem actually is.

           

          You need to give more precise information about your own setup.

           

          You've got 100,000 records.  What does each record represent?

          Is "Client's sector" a field in those records or from a different table?

          Is "Sponsor's sector" a field in those records or from a different table?

          You mention portals - which indicates other table occurrences - what are your tables - and what are the relationships between them?

           

          Looping through 100,000 records as you've discovered is not a very efficient method.

           

          It sounds like what you are wanting might suit relationship filtering based on global fields that are your "search fields" and using multi-key relationships.  But to be able to help you we need more precise information.

           

          Chris

          • 2. Re: Find Records on Multiple Critieria
            happyez

            You've got 100,000 records.  What does each record represent?

            Is "Client's sector" a field in those records or from a different table?

            Is "Sponsor's sector" a field in those records or from a different table?

             

            > Hi, yes I'll stick to the topic at hand.

            There is a main table which most other tables revolve around: the Organisation table. This represents the sponsors

            Another table is: Events table. This is the activity of those who are sponsored (an event, festival or conference)

            They operate as two separate tables from each other.

            Now, in order to note, for each, what sectors they operate in, I have another two tables attached. Let's call them

            a) Org_sectors

            b) Event_sectors

            a) and b) appear as a portal on the main layouts representing most of the Org and Events tables

             

            -

             

            You mention portals - which indicates other table occurrences - what are your tables - and what are the relationships between them?

             

            > the relationship is by linking the Org <-> Org_sector tables by a SN. Same with Events <-> Events_sectors

            When I set up the loop originally, I had the client fill in what sectors they were involved in (since clients are events organisers or NFP charities, they would fill into the Events_sectors table)

            I also asked them who they want to sponsor their event. They would fill in the Sector field within Org_sectors (they want sponsors/organisations to sponsor them)

            I would run the list of fields I mentioned above, I guess, on top of the main find loop which is making sure each of the sectors they write down exactly match the other sectors (if they are in the three sectors of Non Profits, Education, Health; and they want three sponsors within the sectors of ICT, Finance, Arts, then I am doing nine separate finds)

             

            Now, ontop of that, I want to know if they want to include lifestyle brands. If they tick yes, then I include in the find, whether the Field:Lifestyle is not empty.

            Also, what cities do they want the sponsor to have sponsored within? If the client is holding an event in NY, I need to find whether a potential sponsor has bothered to sponsor an event previously in NY. So, they have a choice of up to 12 cities (SF, LA, NY, Chicago etc). These are also thrown into the find.

            Plus, do they want to attract a professional crowd to their event, or is it for anyone of any type? So, they choose 'Consumer' and/or 'Professional'

             

            But another way of putting this is

            • Org_sector (any set of values up to 3)

            • Events_sector (any set of values up to 3)

            • Lifestyle (boolean)

            • Cities (any set, infinite, but likely up to 12 at the most)

            • Prof/Cons (a defined value list of up to 2)

             

            In the future, I may add another field or two, and they will either be uncapped or capped/defined or undefined values.

             

            Does that explain it more thoroughly?

             

            -

             

            Looping through 100,000 records as you've discovered is not a very efficient method.

             

            It sounds like what you are wanting might suit relationship filtering based on global fields that are your "search fields" and using multi-key relationships.  But to be able to help you we need more precise information.

             

            > I hope that helps.

            It was suggested that this set of finds could just be a constrained or expanded find request set. Is this a better way of doing it?

            Thank you all

            Eric