Can the following calculation be expanded on so that it only lists unique values?

Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " )

filtervalues( Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " ) ; FIELDNAME::RESULT )

only problem is FilterValues needs the ¶ to separate the values

Maybe Filter( Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " ); "0123456789" )

this will only filter numbers 0 -9

eg:

input text = (403) 242-0092

after {Filter( Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " ); "0123456789" ) }

would return 4032420092

-i

I don't follow what FIELDNAME::RESULT needs to be.

If my original calculation returns '33 Q Q', I would like the modified calculation to return '33 Q'

I don't follow what FIELDNAME::RESULT needs to be.

If my original calculation returns '33 Q Q', I would like the modified calculation to return '33 Q'

what are you trying to accomplish?

what are you trying to accomplish?

what are you trying to accomplish?

The List() function is returning 33 Q Q from the Assessment Questions::Hazard No field from three related records which is accurate. The only value that will get repeated is Q. Q means that the record has a bespoke question and answer. In this case there are two bespoke questions (Q Q). I want to use this calculation field on a report and I don't want the Q appearing more than once in this field.

The List() function is returning 33 Q Q from the Assessment Questions::Hazard No

is this always the same;

if so; then

substitute( List ( Assessment Questions::Hazard No ) ; ["Q Q" ; "Q"] ; ["¶"; "" ] ) ; however if you have leading and trailing numbers, they have to be dealt with using

left and right functions.

but what the multiple [ ] allow more options for searchstring and replaceString for the substitute function.

*more convoluted without a custom function

let( $string ="33 Q Q 1234 "  // use  List ( Assessment Questions::Hazard No );



Left ( $string ; position( substitute( $string ; ["Q Q" ; "Q"] ; ["¶"; "" ] ); "Q" ; 1;1 ) )

)

is this always the same;

Unfortunately not, I have a table with 35 hazard code records. The hazards are numbered 01 through 35. These numbers will only appear once for each record. I have decided that for bespoke questions that the hazard value will be Q. I could go Q1, Q2, Q3....etc but felt it not necessary because each record has a unique ID primary key. An item could have all 35 hazards and 3 bespoke records and therefore the following value will be returned through my original calculation:

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Q Q Q

I would like the following value returned with only one Q:

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Q

What I am asking is probably difficult as there is no pattern as such. The only trend is that there could be any number of Q's and I only want Q to show once in the calculation field.

If it can't be done I will need to have a rethink.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Q Q Q

I would like the following value returned with only one Q:

Hows this:

let ([

$string = List ( Assessment Questions::Hazard No) ;

$string2 = substitute( $string ; ["Q Q" ; "Q"] ; ["¶"; "" ] ) ;

$result = Case( ValueCount( filter( $string ; "Q") ) ≥ 1 ; "Q" ; "" )

];

$result



)

Suggest you learn more about FilterValues:

Let([

uniqueList = list( 1;2;3);

thisList = list( 1;3;3;3)];

FilterValues( uniqueList; thisList)

)

Result:

1

3

Let([

uniqueList = list( 1;2;3);

thisList = list( 1;2;3;3;3)];

FilterValues( thisList; uniqueList)

)

Result:

1

2

3

3

3

Hows this:

let ([

$string = List ( Assessment Questions::Hazard No) ;

$string2 = substitute( $string ; ["Q Q" ; "Q"] ; ["¶"; "" ] ) ;

$result = Case( ValueCount( filter( $string ; "Q") ) ≥ 1 ; "Q" ; "" )

);

$result



)

Something must be not quite right as FM won't accept it.

let ([

$string = List ( "Q";"01";"02";"03") ; //Substitute List ( "Q";"01";"02";"03") for List( Hazard::No )

$string2 = substitute( $string ; ["Q Q" ; "Q"] ; ["¶"; "" ] ) ;

$result = Case ( ValueCount( filter( $string ; "Q") ) ≥ 1 ; "Q" ; "" )

];

$result

)

let ([

$string = List ( "Q";"01";"02";"03") ; //Substitute List ( "Q";"01";"02";"03") for List( Hazard::No )

$string2 = substitute( $string ; ["Q Q" ; "Q"] ; ["¶"; "" ] ) ;

$result = Case ( ValueCount( filter( $string ; "Q") ) ≥ 1 ; "Q" ; "" )

];

$result

)

Thanks for all your replies imoree but this gives an error message saying 'List usage is not allowed in this calculation'

Suggest you learn more about FilterValues:

Let([

uniqueList = list( 1;2;3);

thisList = list( 1;3;3;3)];

FilterValues( uniqueList; thisList)

)

Result:

1

3

Let([

uniqueList = list( 1;2;3);

thisList = list( 1;2;3;3;3)];

FilterValues( thisList; uniqueList)

)

Result:

1

2

3

3

3

Hi BruceR, when using FilterValues I guess I have to tell FM what success looks like i.e uniqueList?

I though somehow that I wouldn't have to do this because I'm only asking for unique values. I didn't think that I'd have to tell FM waht unique values are. I guess this is required with the FilterValues function.

If uniqueList = list (01; 02; 03; 04; 05; 06; 07; 08; 09; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; Q) can your example be worked into Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " )?

Thanks for all your replies imoree but this gives an error message saying 'List usage is not allowed in this calculation'

put it in data viewer and you get a result

If you need to put in your database.

Create a calculated field or use script call

set var ; $script =

let ([

$string = List ( "Q";"01";"02";"03") ; //Substitute List ( "Q";"01";"02";"03") for List( Hazard::No )

$string2 = substitute( $string ; ["Q Q" ; "Q"] ; ["¶"; "" ] ) ;

$result = Case ( ValueCount( filter( $string ; "Q") ) ≥ 1 ; "Q" ; "" )

];

$result

)

Then when you want data, just call the $script variable just created.

An old but simple technique that you can use is create a value list from the field ( if its indexed ) and then simply use ValueListItems ( ), otherwise you can use a custom recursive function since you have advanced. There should be some already out there.

let([

unique = list( 0; "Q");

thisList = list( "Q"; "Q"; "Q"; 1; 2;4;2)  //Substitute for list( Attributes::HazardNO

];

getvalue( filtervalues( thisList; unique)  ;1 ) //since we only want 1 value there if not will return all Q's

)

that is thanks to BruceR's comment

ValueListItems as John said is one way to do it. If you want to explore other options, try searching for "List" or "Unique" at www.briandunning.com/filemaker-custom-functions/

Hi BruceR, when using FilterValues I guess I have to tell FM what success looks like i.e uniqueList?

I though somehow that I wouldn't have to do this because I'm only asking for unique values. I didn't think that I'd have to tell FM waht unique values are. I guess this is required with the FilterValues function.

If uniqueList = list (01; 02; 03; 04; 05; 06; 07; 08; 09; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; Q) can your example be worked into Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " )?

That is the point of suggesting that you learn about the technique.

You said you already had the unique list; the known values for your question number.

Why don't you try it and tell us?

That is the point of suggesting that you learn about the technique. You said you already had the unique list; the known values for your question number. Why don't you try it and tell us?

Well, I think I finally have it after I realised that I had to break up my original calculation and put the substitute() function around the outside of flltervalues():

Substitute (Let([

uniqueList = List("01"; "02"; "03"; "04"; "05"; "06"; "07"; "08"; "09"; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; "SPAD Alert"; "Q");

thisList = List ( Assessment Questions::Hazard No )];

FilterValues( uniqueList; thisList)

)

; ¶ ; " " )

Are the words 'uniqueList' and 'thisList' recognised FM terms or are they just random text facilitated by the Let() function?

Those are arbitrary names for the variables in the Let function. They are not reserved words. Use whatever names you prefer.

I like to use descriptive names in general, but I try to keep them short. I tend to use one-letter variable names for things like counters (i, n), days (d) or hours (h).

Here is a variation on your calc that may be easier to read and understand the sequence:

Let([

uniqueList = List("01"; "02"; "03"; "04"; "05"; "06"; "07"; "08"; "09"; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; "SPAD Alert"; "Q");

thisList = List ( Assessment Questions::Hazard No );

filtered = FilterValues( uniqueList; thisList);

result = substitute( Filtered; ¶ ; " " )

];

result

)

This would solve it...

First, create the following 5 custom functions, in the following order:

SingleBreaks(text)

-----------------------------------

If (PatternCount (text ; ¶ & ¶)>0 ;

singlebreaks(Substitute (text ; ¶ & ¶; ¶))

; text)

####################################

NoTrailingBreaks(text)

------------------------------------

If (Left (text; 1) ≠ ¶;

If(Right(text ; 1) ≠ ¶;

text;

Left(text ; Length(text)-1)

);

Middle(text ; 2 ;

Length(text)-(1+(Right(text ; 1)= ¶))

))

#####################################

CleanupBreaks(text)

-------------------------------------

notrailingbreaks(singlebreaks(text))

#####################################

RemoveDuplicatesSub(values)

-------------------------------------

If( ValueCount(values)>1 ; removeduplicatessub(notrailingbreaks(LeftValues(values ;ValueCount(values)-1)))

& ¶)



& If(PatternCount(¶ & Substitute(values;¶;"¶¶" )& ¶; ¶&GetValue(values;ValueCount(values)) & ¶)<2; GetValue(values; ValueCount(values)))



#####################################

RemoveDuplicates

-------------------------------------

cleanupbreaks(removeduplicatessub(values))

#####################################

Then just change your calculation to Substitute ( RemoveDuplicates(List ( Assessment Questions::Hazard No )) ; ¶ ; " " )

Can the following calculation be expanded on so that it only lists unique values?

Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " )