Finding no. of duplicates in the current found set

[modles]

hey guys, this is a really driving me nuts!

Im still battling away trying to find a way of performing AND searches in portals. The only way i can see of doing now is this..

perform OR finds on the related table (actually from that table layout). Then to filter the list i need to omit the records which don't match all the criteria. I.e only keep records where the number of duplicates is less than the number of OR requests in the search. Does that make sence.

Obviously im still open to any suggestions as to an easier way of doing this. A screen grab of my database can be found at http://foxmurphy.destinet.co.uk/screengrab.pdf

Here you will see the main table (candidates) and the portal of related records (skills). I need to come up with a way of finding candidates which match multimple skills specified in a search. Eg someone who has both 2 years experience in Sales and 1 years experience in Auditing (to use the screengrab as an example candidate). It seems impossible

Oh and before anyone says it i cant use checkbox fields or repeating fields as i need to match up yrs experience, sector, house etc etc, not just one related field. So i cant just have skills as a checkbox field in the candidate table

Please help. Im not sure what the deal is on this forum but if it is alowed im happy to pay pal someone $100 for the answer. (If its against forum terms please let me know and i will edit this post)

Regards

Ben

UK

[Ender]

Hi Ben,

How about performing your search in the Skill table, like you're doing, then go to the related Candidates like this:

#After finding all skills that match the criteria

Go to Layout [ Skill layout with only CandidateID on it ]

Copy All Records

Go to Layout [ Skill developer layout (with gCandidateIDs) ]

Paste [ gCandidateIDs ]

Go to Related Records [ Show related ; Relationship: Candidate by gCandidateIDs ; Layout: Some Candidate layout ]

This assumes you have a global text field 'gCandidateIDs' in Skill, and another relationship to Candidate based on that global.

[Søren Dyhr]

Oh and before anyone says it i cant use checkbox fields or repeating fields as i need to match up yrs experience, sector, house etc etc, not just one related field. So i cant just have skills as a checkbox field in the candidate table

Would there be any chance of utilizing applescript or are the rest of the users on windows??

--sd

[Ender]

Hmm,

It looks like what I offered won't help you get your AND results, it will only filter your existing found results to the candidates table.

[modles]

yeah users will be pc and mac

[modles]

yeah this is the problem, it will take me to a list of candidates that match one or more of the criteria not all.

how about this.... kinda long winded but...

once i have a recordset of skills (from my OR search), I order them by candidateID, then set a loop which enters a count value in each record when the candidateID matches the last CandidateID (In other words if the candidateIDs of the found set were CA1, CA1, CA1, CA2, CA2, CA3 - i would generate 1, 2, 3, 1, 2, 1 respectively). Then I would have to perform a contraining find which looks for all records where this count value equals the number of requests in the original OR search, which in this instance would be 3. (I would have to generate this number by the use of a script to generate each new request)

Then I should have a found set of records which all match all the criteria. correct?

I can then do the whole copy all records and goto related records.

It just seems like a real slag of a solution, i'm amazed there isnt an easier way

ben

uk

[transpower]

You may want to look into ODBC and performing an outside SQL query into your database. On the Windows side, MSQUERY is available (included with the operating system or Microsoft applications) and could be used; with this you could combine any number of AND's and OR's. Also, since you're on 7, you could use the Relationships Graph to construct a graph of your query, using multiple table occurances, with the final results being displayed in a portal.

[modles]

hmm could do it that way but it seems a little messy. But hey probably no more messy than my idea.

I have tried the idea i stated above and it does work fine!, i just now need to work out how i can incorporate it into the search on the main candidate file too. But i guess as long as i get them to perform the candidate table search after i have found the records matching the skills then i can just contrain the set.

if not i will have to do the two searches seperatley and then omit and records from the skills found set that do not contain the candidateid from the main search.

ben

[Ender]

Ben,

Attached is a different approach using a portal filtered on all the possible search criteria. I just used Skill, YearsExperence, and Sector, but you could add more criteria. You select the options in the global search fields, then see the results in the portal.

The search results' CandidateIDs are then added to a Search results table. A script then 'AND' combines the lists of CandidateIDs to come up with the final list of CandidateIDs that were in each Search result. Finally you see the result found set in a Candidate List layout.

I'm sure you could work the interface differently, but this should at least give you another way to approach it.

Candidate.fp7.zip

[Ender]

It occured to me that there might be a good ValueListItems() trick to building those CandidateIDs multikeys more dynamically. Unfortunately I have some real work to do right now, so I can't look into it.

[modles]

cheers for doing that buddy, not sure its giving me anything more than the count method above, however im going to have a good look and see how it could be fitted into the rest of the solution. The number of relationships needed is probably going to cause me a prob but I will see.

[Fenton]

I think I got something. It uses a Loop on the repetitions of the criteria global fields; which are lined up so you can set them for multiple lines.

The crux of the method is the FilterValues( field, values) function. Copy All Records captures the IDs from each Find. On the 1st Find it sets a multi-line _gCandidateIDs field with the result. Then each successive Find filters that field with the resulting multi-line IDs of the Find. If any of the Finds fail, the script Beeps and Halts.

What's left is only the CandidateIDs of records matching all of the criteria (I think -).

[Example file modified and moved to a later post.]

[Ender]

Not bad Fenton!

By using repeating fields and Finds, your relationship graph is much simpler. I wonder how they compare in performance?

[Fenton]

Well, relationships might be a little bit faster. I see that we're doing very similar things, using FilterValues(). Mine is a little simpler in structure, but a bit longer in script.

What I wondered about (common to both solutions) was just how fast that FilterValues( field, values) step would perform, with large multi-line fields on each side. I imagine FileMaker can do it pretty quick. But the new factor here in 7 is that both sides can be globals, so it's a little different from comparing a global to a stored index. It would be interesting to see how this works on a large record set.

[modles]

This works really nicely, same result as mine, however its easier to build into the standard interface.

Is there any way of getting it to only search records in the current found set. I.e if you do a standard find on the CandidateName field (say put a 'j' in there to give you joe and jess) Can the skill search be then based on this current found set not all the records?

Ive just had a panic. what about if people now want to search for Accounting OR Sales. I know its crazy in the fact that this is what filemaker does as standard, just trying to find a way to build an interface that can handle both.

maybe i could put a drop down at the beginning of each line with (AND / OR). But as i dont 100% understand your solution, im finding it hard to work out how i could build that in. This is probably a bridge to far, but could i give them an overall option to either do an OR search or and AND search (not combined)

ben

[Fenton]

Ah, "feature creep" -)

There are 2 basic techniques in the overall method. The first is the Find, using the repetitions. The second is the FilterValues(field, values).

We are using separate Finds now, returning basically "Or" results. Then we're using Copy All Records to put those results into a field, both for temporary storage - and so we can use FilterValues() to match one Find's result of ID's with the next's; essentially producing a kind of AND result from multiple OR requests (kind of a brain twister, but basically "find people who match all of these criteria AND all of these other criteria, in a table with multiple records per person).

To produce a straight OR result is easy. Just run it as only 1 Find, each repetition in its own new request. You don't need to "filter," and you only need Copy All Records for the final result, so you can flip back to those people in the Candidate table.

As far as "filter by a letter(s)" of their name, that's doable, by including it in the Finds. But you'd want to bring the letter(s) INTO the Skills table, as a Lookup; as there is no "name" in the Skills table. Both the Letter and the And/Or toggle could be globals on the Candidate layout, easy enough.

Now, I don't want to increase your panic, but there is another (remote) possibility. Which is to be able to choose AND or OR for EACH row of repetitions. It is also doable, but begins to beg the question, "Do they want it?", and its companion, "Is it worth it?"

It would not be terribly difficult to gather the AND requests and do them together (as we've done), then do the OR requests successively at the end.

But, if they wanted something like: row1 OR row2 AND row3 OR row4 AND row5. Well, it would theoretically be doable, but it makes my head hurt to even consider what the script(s) would look like. And without () to specify groups you could only guess what was really intended.

However, if you had toggles for And/Or next to multiple rows, the interface would allow it. I say, if they want this, then you should limit the number of rows; only as many as you can stand :-) And it would be done in the order entered, with no grouping.

Or you could just tell them to go to the Skills table, enter their own requests, in the correct order, using new requests, Extend Find or Constrain Find to get the intended result. Not an option...

[Fenton]

Here is the example file again, modified somewhat to do the AND/OR toggle. It's still not perfect. The repetitions are a little clunky if you change your mind, and want to delete one of the criteria. You can just make one of the rows blank, and that works fine, but the others don't "slide up," like a portal would. It's mostly a cosmetic thing. Not worth scripting to reset, IMHO.

You could use a separate table for the criteria. Then when you delete one, the others slide up. But that is somewhat awkward to use with the OR Find. I did that also, but it was kind of "flashy," as it used a new window for setting criteria (so the main window could stay in Find mode).

Of course, if you used relationships, like Ender did, then you don't have to use Finds at all; solving that problem. To use relationships with an OR "find," you would simply add the 2 resulting captured global IDs together (concatenate), rather than using FilterValues().

Candidate_g_fej.zip

[modles]

Hi Fenton

So sorry for not responding earlier, ive been hit with the lurgy for the last few days, not felt like doing much developing

anyway, that file (with and or) is fantastic, thank you so much. im going to spend this weekend implementing it across my app. I hope it all goes well.

I really do appreciate this... you will have to give me your address and ill send you a thank you present

Ben