List () calculation

[adyf]

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

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

[imoree]

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

[adyf]

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'

[imoree]

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?

[adyf]

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.

[imoree]

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 ) )

)

[adyf]

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.

[imoree]

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



)

[bruceR]

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

[adyf]

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.

[imoree]

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

)

[adyf]

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'

[adyf]

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 ) ; ¶ ; " " )?

[imoree]

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.

[mr_vodka]

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.

[imoree]

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

[Fitch]

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/

[bruceR]

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?

[adyf]

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?

[Fitch]

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).

[bruceR]

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

)

[databaser]

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 ) ; ¶ ; " " )