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

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.

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!

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.

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,

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.

Perfect, thank you.