Related Searches, Global Fields, Partial Matches

[Harry]

Hi Board!
 

I've read a couple of threads on this, but i'm not getting it.... 

 

I have a Months table, that calculates the months (36 records for historic charting).

 

I have a joined table occurance, 'Products_Checked'. This lists all the products that have been checked, other details, and their date/timestamps.

 

I want to chart a Count Per Month of Products Checked. But i want to only count certain products - namely with a stock code that starts with ' 50 '.

 

I have a Global field in the Parent Months table that is my 'Search String' but if I put 50 into it, the graph doesn't work.

 

If i remove the relationship for this search, then the Count works for all products.

 

I have tried putting in 50*, which works for a search, but not for a graph.

 

How do i put a wildcard search string into a relationship for this type of filtering?

 

Thank you again and again.

 

Harry

[comment]

I have tried putting in 50*, which works for a search, but not for a graph.

 

I think you mean "works for a search, but not for a relationship". That's true - a relationship using the = operator requires an exact match. However, you are not required to use the = operator; you can use <, ≤ , > and ≥ to narrow the related records to a range (the exact method depends on the nature of the data - basically you need to calculate the upper limit of the range).

 

 

Another option is to use a custom function (or a repeating calculation field) to "explode" the (existing) values, so that a product code of "50123A3" for example, is tuned into a multi-key:

 

5

50

501

5012

50123

50123A

50123A3

 

This multi-key field will match any of the listed values, so the above record will be related if you enter "50" (or "5" or "501" etc.) into the global field.

[Harry]

Thanks Comment. Some of my stock codes are 501234-56 or 505432-12, so they're text fields. I'm not sure my GrtrTh/LessTh Global fields are working on those text fields.... could that be the case? If it is, I will need to look into these exploding multi keys!

[comment]

I'm not sure my GrtrTh/LessTh Global fields are working on those text fields.... could that be the case?

 

I don't see why not, but if they are text fields, then the comparisons are done alphabetically.

[Harry]

Just does not seem to work doing a 'From 500000' to '509999'.

 

Some codes might be '501111-11' or '50EDA1-89'

 

Can you offer some help with the Custom Function? I have only used one once, a long time ago; and it was pre-prepared.

 

This is what i have:

 

It's the MultiKey you linked to.

 

 

 

Case (

 

Length ( Text ) > 1 ;

 

FieldBeginsWith ( Left ( Text ; Length ( Text ) - 1 ) ) & "¶" & Text; Text

 

)

 

Do I just write out the Field in those parenthesis for 'Text'? (i.e. Dashboard_MONTHS:Data_Search_Text) 

 

and then again for each of the Text occurances within that Case statement?

 

Cheers,

[comment]

Just does not seem to work doing a 'From 500000' to '509999'.

 

That would work for "501111-11" but not for "50EDA1-89" - because alphabetically "509999" comes before "50EDA1-89". However, both "501111-11" and "50EDA1-89" are between "50" and "50zzz" (or, if you want to play it more safely, "50ÿ" or even higher*).

 

 

Re the custom function, see:

http://fmforums.com/forum/topic/69831-calc-to-create-exploded-array/?p=330657

 

Once you have created the custom function, you use it as any other, e.g. =

Explode ( ProductCode )

inside a calculation field whose result type is Text.

 

 

---

(*) Caveat: this also depends on the fields' indexing language setting.

[Harry]

Perfect, thank you.