Multiple line portal search?

[dvalley]

In our solution, we have several layouts which have portals containing data to be searched. Our people want to be able to perform searches for several items in the same field in the portal (for example: catalog number [master record], components [ portal records] h search: how many catalog numbers have components x, y and z?). The portal will only show one record in find mode. If you go to the line items layout, how do you link the components to a catalog number? HELP!!

[Søren Dyhr]

how many catalog numbers have components x, y and z?

Is it the question, if we have 30 of x, 25 of y and 10 of z - which sets/assemblies are then possible in an orderline??

If it's the case, then must I warn you - It should be solved with at recursive many-to-many structure, causing a lot of either unstored calc's talking to each other ...or dedicated design with the transaction model as ideal. Both are indeed posible with filemaker ...but hardly newbe stuff - I'm afraid!

Read this: http://www.amazon.co.uk/Advanced-FileMaker-Techniques-Developers-Wordware/dp/1556228597/sr=1-1/qid=1167432611/ref=sr_1_1/203-5924459-8348714?ie=UTF8&s=books

--sd

[dvalley]

Let me be more explicit: We are a small chemical manufacturer who make a lot of custom mixes. Because of the myriad of synonym variations and trade names for chemicals, a classification number (CAS) number was invented. When we quote a custom mix, we will often search previous quotes for similar chemicals (via CAS numbers). This helps us price and determine how feasable the mix will be to make. In the past, we had repeating fields to hold the CAS numbers and could search records based on multiple CAS numbers on a given record. Now, we have eliminated repeating fields, replacing them with portals. The problem we have encountered is that only one portal line can be searched upon. This means one CAS number. I have been told that the normal maximum number of CAS numbers searched rarely goes beyond twenty, but we do have mixes up to 214 components. How would you recommend we search?

[comment]

You will need to script this kind of search, I think. Roughly, the script would be given a list of chemicals to look for. It would find the quotes with the first chemical, then loop: constrain the found set looking for the second chemical in the list, and so on.

[Fenton]

I would agree with comment that if a search is needed it should be scripted. But I don't get why a search is needed. It seems like there would be a relationship here, in which case you would just Go To Related Record, based on the portal's relationship. That would give you the child records in the portal.

It would also seem that you would need either a join table (best), or a multi-line field with a self-relationship, in order to link a chemical to its "similar" CAS numbers.

Basically it all seems very relational, but you haven't really described your structure, so we can't say what would work best.

[comment]

If I understand correctly, the task is similar to finding invoices with a given combination of products. To do this through a relationship would be quite complex, given that such combination is unstored at invoice level.

[dvalley]

This is closer to what we want. We have a Quote (parent record)which stores the catalog number of the chemical mix. The child records (in the portal)contain the individual chemicals which make up the product (catalog number). We wish to search for what catalog numbers are made up of what chemicals. It is the combination of the chemicals in the product that are important here.

To put it simply: A recipe file. What recipies have eggs, flour and milk in them?

[Fenton]

Well, I can think of a way to do it relationally, with a script using the Filter() function; which would work well with the List() function (requires 8.5). But comment is right, a Find, with Constrain Finds for each successive CAS would be simpler.

You just need to get those CAS numbers you want into a global field, in some manner easy to get them out. Find the 1st one (in the child table), then use Constrain Find to do each of the others.

[Søren Dyhr]

A major problem is that you in findmode can't enter more than one portalrow, in each request - so you have to emulate the portalish look.

ANDSEARCHES with portals have after FM7 become much more convoluted than Jon Rosens method ...

Anyway I have given it a stab, the only trouble I havn't found a solution to if the portal contains the same item several times in more lines....

--sd

doit.zip

[Fenton]

This is more what I think comment had in mind. It's simpler. The only caveat I see right off is that you couldn't have a blank line in the repeating field, and expect it to continue past it. But that could be solved using a comparison on Last (repeating field). More trouble though.

doit_fej.fp7.zip

[comment]

I don't quite see the advantage of a repeating field here, compared to entering the search values into a global text field, separated by a carriage return - specially when "we do have mixes up to 214 components".

In any case, a blank line in a repeating field is easy to deal with in v.8.5. For example, you could make your script parameter =

List ( RepeatingField )

then use the parameter as the source for search criteria, i.e.

GetValue ( Get (ScriptParameter) ; $counter )

A comparison on Last ( RepeatingField ) doesn't seem very reliable, because user could enter the same value twice - a very easy mistake to make with 214 components to search for...

[Fenton]

Yes, I agree. A simple global multi-line would be better; easier to clear, less problems overall.

[Søren Dyhr]

Indeed, it was just remains from a test if I could make Jon Rosens template behave dispite the changed nature of repeaters! Similar is it an old habit to shift the searching to childside of the matter, not quite justified any more.

The conversion from repeater to a global textfield or a $variable with via List( is a fine detail MIchael, however does it still have some point, providing the user with a similar looking interface, giving metaphoric associations.

--sd

[comment]

I would agree on principle, but not in this case, where "the normal maximum number of CAS numbers searched rarely goes beyond twenty, but we do have mixes up to 214 components." I would also imagine that the required components are already available in some form, so it would be nice to be able to paste them into the solution.

With a single global field, the script could be as simple as this:

Go to Layout [ “List” (Parent) ]

Set Variable [ $counter; Value:0 ]

#

Loop

Set Variable [ $counter; Value:$counter + 1 ]

Exit Loop If [ $counter > ValueCount ( Parent::gSearchCriteria ) ]

#

Enter Find Mode [ ]

Set Field [ Child::Value; GetValue ( Parent::gSearchCriteria ; $counter ) ]

#

If [ $counter = 1 ]

# FIND THE FIRST ONE

Perform Find [ ]

#

Else

# FIND THE REST

Constrain Found Set [ ]

End If

End Loop

[dvalley]

Dear Comment:

I tried your script example, and so far, it works for us. I have put it out to test with out tech Service folks who will try to break it (if I know them). I was leery at first, until I realized that the global field used hard returns between the search entries (I had to look up the GetValue function to figure that one out). I have not explored the higher level scriping functions much, despite my 20 years of FileMaker experience. Thanks. I will look at the other scripts submitted. I need all the help I can get. Thank you all.

[Søren Dyhr]

You're excused, GetValue( arrived with fm8 and Constrain... arrived with fm6 so the years 2005 and 2002 is hardly any time compared with the 20 you mention.

--sd

[comment]

I have put it out to test with out tech Service folks who will try to break it

Let them. :bigshades:

[dvalley]

OK, I have given the script a try. For the most part, it works, but if I search for five items, I may get back records with only one of the items on it, not necessarily all five. I have also found out that they might want to add to that search with requests in the parent record (such as solvent or number of components). I could get it to work with the solvent request, but when i added the number of components field (a calculation based on the number of daughter records), the script took more than five minutes "scrolling" before I canceled the request. My people will be very upset if searches take longer than a minute or so...