Logical AND with multi-line key

[genevieve charbon]

Hi,

Multiline-key are great but they used as logical OR, which is usefull for a lot of situation. But now I need a logical AND.

I've a database of products,

Table Products

ProductA

ProductB

and a table of product attributes

ProductA Attribute1

ProductA Attribute2

ProductA Attribute3

ProductB Attribute3

ProductB Attribute5

ProductB Attribute6

I wish to go (with a go to relationship step) to all products who have both Attribute2 and Attribute3

so in that case I would get A

It's as If I had a car database and I want the user to be able to get all the cars that have manual geard and are blue

A multiline key, populated by the user using checkboxes

Attribute2

Attribute3

would work if only it was possible that the multi-line key was considered as AND rather than OR.

how to do this

thanks

[Søren Dyhr]

The entry is when a checkbox delimited by ¶ which could be substituted with a pipe | in both sides of the relations def.

http://www.filemaker.com/help/FunctionsRef-381.html

...comes awfully handy in here! Meaning that a calc'field is requred in both ends.

--sd

[genevieve charbon]

Thanks

But I wanted to avoid calc fields on both sides because I can't preduct if the user will check one of all attributes.

So the user entry would be

Attribute2|Attribute3

I doubt it would match this key on the other side

Attribute1|Attribute2|Attribute3

moreover, on the other side, how I create that key ? I would need a self relationship on the product key, and so the key would get unstored and hence useless for a relationship

am I missing something here ?

[comment]

The key on the left side needs to look like:

Red | * | Blue

and on the right side (assuming a record with all three colors):

Red | * | *

* | Green | *

* | * | Blue

Red | Green | *

* | Green | Blue

Red | * | Blue

Red | Green | Blue

With anything more than a few attributes, a find would be preferable.

See also:

http://fmforums.com/forum/showtopic.php?tid/171912/

http://fmforums.com/forum/showtopic.php?tid/172518

[genevieve charbon]

Thanks Michael,

but in the example you pointed me to,the data part contains on each record the possible atributes. Each attribute is a bolean field.

as I can have thousands possible attribute that's not possible.

So I think I should use a find, but it will be slow

too bad that fmp can't do a AND kind of request

[comment]

too bad that fmp can't do a AND kind of request

Huh?

[genevieve charbon]

Yes, I mean in a relationship

if I could have had

Attribute2

Attribute3

in a key,n and had a matching operator ine the relation ship like AND, My problem (and those of thousand fmp dev would have been solved).

In fmp relation ship you can do something like

select fi where attribute= Attribute2 OR attribute= Attribute3

that's waht the multiline key gives you

but not

select field where attribute= Attribute2 AND attribute= Attribute3

[Søren Dyhr]

This is nonsense this isn't requests at all, but relational mechanics, OR'ing is here the tough part AND'ing is straight forward the way you stack relations in the def. dialog.

You seem once again too eager to bash the tool as such, please humble yourself to the tools realm instead of yelling up about inadequacies in advance. This tool as well as every other tool you encounter on your way, needs you to hesitate a little, in order to make you use it the way it's supposed to be utilized.

Could you make me a roster of tools acting to whims only?? It escapes me why you seems so eager to pinpoint felt inadequacies as soon as you are just a tiny bit stuck???

--sd

[comment]

Multi-line keys are a convenient shortcut, but they have their limits. The problem is that you don't want:

attribute = "Attribute2" AND attribute = "Attribute3"

which is a contradiction, but:

attribute CONTAINS "Attribute2" AND attribute CONTAINS "Attribute3"

This is a job for the find engine. Though you COULD do this with a relationship, by having a separate field (on the left side) and a predicate for each criteria.

[genevieve charbon]

Yes Michael you're right, this can't be AND for what I wanted to do.

My mistake.

[genevieve charbon]

I found a solution that works for me, with no key field in the right table

The idea is that rather than using the checkbox field as the real key used to display the result, I use this checkbox field as a temporary list of attributes that I will process, to generate a multiline key that will be used as the key to display the resulls.

So in my example, the user check Attribute2, and Attribute3, so I get this user generated multiline key I store it in a variable which i call $list

so $list =

Attribute2

Attribute3

Then I will get each value of that key, set a global attribute key with this value, and with a valuelist get all the product_sku that matches this particular attribute.

so I get a $found_ref multi-line variable that contains every product_sku used by a particular value,

Then I use the fitervalue function to filter that $found_ref to the previous_one and store the filtered result in a $matching_found_ref.

So let's say Attribute2 matches productA,productC,productZ,productX => $previous_found_ref

and Attribute3 matches productC,productZ,productY => $found_ref

so I then used the filtervalues($previous_found_ref;$found_ref) and get productC,productZ whch matches them both.

I then put productC,productZ in a final multi line key

final-Key =

productC

productZ

I then use this Key to display the results.

So I need one relationship based upon a global value for finding the product with that current attribute, and if I want to another relationship to display the results.

I hope this will help some folks.

In attachement I've put the script.

You can ignore the firstline as it is used just for testing purpose with attaibute 42 and 1138 : you should put the user checkboxed filed in that $list variable.

[Søren Dyhr]

Wouldn't it be better to upload the xml clippings, since the readability is next to nothing in your present upload!

The "nice to know" vs. "need to know" ration is somewhat askew here!

--sd

[genevieve charbon]

How do you do xml clippings ?

or script steps as text as I see in the forum sometimes

[Søren Dyhr]

Well the Clip Manager 2 (3) allows "Save as" ... but otherwise could you during printout copy and paste from the preview ... but the indents will then be ignored, and you have to manually adjust the text.

Then include the entire clipping in code-tags instead of quote-tags!

--sd

[comment]

Not all of us use Clip Manager, so it's best just to copy from Preview. The indents are not important.

[Søren Dyhr]

Not all of us use Clip Manager

But we do all use macs : - I wonder if windows users need to install something in the vicinity of CutePDF and copy/paste from that??

--sd

[genevieve charbon]

Ok, here it is thanks for the preview trick

of course the $list is initialised her just for the purpose of demonstration, normally it should be set to the checkbox field

Set Variable [ $list; Value:"Attribute2¶Attribute3" ] 

Set Variable [ $found_ref; Value:"" ] 

Set Variable [ $matching_found_ref; Value:"" ] 

Set Variable [ $i; Value:0 ] 

Set Variable [ $Nbre; Value: ValueCount ( $list ) ] 

Loop 

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

    Set Field [ Bible Articles::Searched caract ID; GetValue ( $list; $i ) ] 

    Set Variable [ $found_ref; Value:ValueListItems ( Get ( FileName ) ; "Products this caract" ) ] 

    If [ $i=1 ] 

          Set Variable [ $matching_found_ref; Value:$found_ref ] 

    Else 

          Set Variable [ $matching_found_ref; Value:FilterValues ( $matching_found_ref ; $found_ref ) ] 

    End If 

    Exit Loop If [ $i ≥ $Nbre ] 

End Loop 

Set Field [ Bible Articles::final _multi_ref_key; $matching_found_ref ] 

[Søren Dyhr]

Alright I'm not sure I would script the matter...

--sd

test.zip

[comment]

What you are doing seems to be exactly what I suggested above (having a discrete predicate for each attribute) - except you're doing it across the time dimension instead of in the graph space.

Which means that on one hand you can add as many criteria as you want without modifying the relationship, but OTOH it needs to be scripted. If scripted, I believe a find would be much more efficient than this.

[comment]

I don't think your file is answering the question in this thread.

the user entry would be

Attribute2|Attribute3

I doubt it would match this key on the other side

Attribute1|Attribute2|Attribute3

[Søren Dyhr]

Well this must mean the thread header is wrong since the yield then is supposed to be less tight than a genuine AND!!!

What will you call it - Omitted XORING or HalfAdder?

--sd

[comment]

In a find, searching for 'Red AND Blue' will find all of these:

Red | Blue

Blue | Red

Red | Blue | Green

Blue | Pink | Red | Yellow

etc.

The question is how to achieve the same result with a relationship. I still call this AND, because the conditions are cumulative. The difference is not the logical operator, but what surrounds it:

The problem is that you don't want:

attribute = "Attribute2" AND attribute = "Attribute3"

which is a contradiction, but:

attribute CONTAINS "Attribute2" AND attribute CONTAINS "Attribute3"

This is a job for the find engine.

[Søren Dyhr]

The question is how to achieve the same result with a relationship.

What about Ugo's method:

--sd

test2.zip

[genevieve charbon]

I'm not sure about the ugokey solution, and the previous test.zip because they use repeating fields and hence seem limited in the number of criteria.

Moreover it seems at leat in ugo key that they rey on record id

my producvt to criteria table is

ProductA Attribute1

ProductA Attribute2

ProductA Attribute3

which means each product can have a unlimited number of attributes, in the proposed examples it's more like if there would only be one atribute per product (and not n attribute for a product)

I'm looking for a solution with limitless (not billions = not decided by the dev) number of criteria, so everything which put limits on number of attribute is not what I'm looking for

But maybe my understanding of your examples is limited, I confess I ddin't understood them fully (but limiting factors seems to be here)

[genevieve charbon]

Which means that on one hand you can add as many criteria as you want without modifying the relationship, but OTOH it needs to be scripted. If scripted, I believe a find would be much more efficient than this.

>Michael

in my experience (at least networked server client), related finds are a lot , a lot slower than this

[comment]

What about Ugo's method

Yes, that should work (provided the sizes are reasonable). I see now that I didn't mention this possibility, but I know I wanted to. :

[comment]

Why related finds, and not directly in the relevant field?

[genevieve charbon]

Because, the fields (attributes) are in a different table !

[comment]

Hm. This rings a distant bell...

http://www.fmforums.com/forum/showtopic.php?tid/174036/

IIRC, the conclusion was that searching on related STORED fields *was* quick enough to make complex alternatives superfluous.

Note that this was before version 8.5 and its List() function.

[genevieve charbon]

> Michael

Your FindCustProd2.fp7.zip looks like the same technique I'm depicting here, am i correct ?

So we fall both on the same conclusion I guess?

I'm pretty sure that go to related script step is much faster than a search, even in the same file.

I say this because I had a slow script. This script was for determining the rank (order) of a product sale by months, among thousands of products and years.

So what I did was looping searching (in the same table) for each month.

It was slow, when I replaced this by a go to related script step, It was lighting fast

This is my memories, I'd need to double check.

[genevieve charbon]

Checked the speed of the above descripted long script,

With search :

21 seconds

With go to related

15 seconds

Ok, the find requires 3 steps (I don't know how to do a variable find otherwise) while the go to related requires only one, but I can't do ortherwie I think

the disabled steps are those used in the find version

Set Field [ stats_ventes::annee_mois en cours; Right(stats_ventes::Liste mois; 7) ] 



//  Enter Find Mode [  ] 

//  Set Field [ stats_ventes::annee_mois; "==" & stats_ventes::annee_mois en cours ] 

//  Perform Find [  ] 

Go to Related Record [ From table: “stats_ventes self anne mois en cour”; Using layout:  ] 

[ Show only related records ] 

[comment]

I don't really remember what I did there, I'd have to look.

Re find: all you really need to do is dump the selected attributes into the searched field, in a single request

(that's assuming single-word values and no conflicts like "Red" and "Redmond" - otherwise it needs a bit more processing).

[genevieve charbon]

Yes Finds are a lot slower than go to related script steps.

I made a file to test it.

With 200 iterations it takes

1 seconds with go to related

2 seconds with standard find (but this one can find Redbull, if we search for just Red)

and 2 minutes 42 seconds = 162 seconds with exact match find "=="" that do not find Redbull if the search is just Red

So, Finds, if used to emulate the go to related field is 162 times slower !

So my advise is to avoid finds if it can be done with go to related.

Of course if the record count is low it doesn't matter

FINDvsGOTO.zip

[comment]

I don't think your test is relevant to the issue at hand. Of course the GTRR is faster - it has a head start from the join cache. The question is: will this hold in your described situation, where a single find competes against repeated GTRRs, each one starting from scratch, and each one having its results filtered by the previous ones?

162 seconds with exact match find "=="" that do not find Redbull if the search is just Red

Try using just "=" which will use the word index, instead of "==" which is equivalent to searching on unstored. Also make sure you don't count the first test, which may include the time required to index the field.

[genevieve charbon]

I don't think your test is relevant to the issue at hand. Of course the GTRR is faster - it has a head start from the join cache. The question is: will this hold in your described situation, where a single find competes against repeated GTRRs, each one starting from scratch, and each one having its results filtered by the previous ones?

There's no single find in my depicted situation. The finds have to be consecutive contrained finds

Because the target table is

SKU Attribute

PA A1

PA A2

PA A3

you can't do a find with Attribute = A2 and Attribute = A3 in one request. Filemaker says use another field, this field is already used in that request

You could do a find if you had that kind of table

SKU AT1 AT2 AT3

PA A1 A2 A3

PB A2 A3

PC A5 A6 A3

But that structure is not usefull to me because it limits the number of attribute a product can have to a finite (here 3) number.

So in my situation we're comparing consecutive filtered GTTR, vs consecutive constrained Finds

Hence my comparison GTRR vs Finds

Try using just "=" which will use the word index, instead of "==" which is equivalent to searching on unstored. Also make sure you don't count the first test, which may include the time required to index the field.

The file is fully indexed (indexing set to all)

5 seconds GTRR

10 seconds Normal find

11 seconds "=" find

But "=" can lead to false result like Red Bull I guess, I know I had trouble with that.

Edited August 5, 2008 by Guest