Fieldvalue filters are used to limit possible choices for a picklist, you can define them by setting column G3E_FIELDVALUEFILTER on table G3E_TABATTRIBUTE.

Suppose you have the following table REF_FSHELF :

If you look at this table each single row is a unique combination of values for MANUFAC, MODEL_NAME, SLOTS & POSITIONS. You can use FieldValue-filters to enable only those values in a menu making up a valid combination. Let’s use this table to create 4 picklists on the following attributes :

You can create the picklists with the following statements :

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD,G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 101, 'MANUFACTURER', 'REF_FSHELF', 'MANUFAC', 'MANUFAC','G3ERestricted', 0);

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD, G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 102, 'MODEL','REF_FSHELF','MODEL_NAME', 'MODEL_NAME', 'G3ERestricted',  0);

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE,G3E_KEYFIELD, G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 103,'SLOT_COUNT', 'REF_FSHELF', 'SLOTS', 'SLOTS', 'G3ERestricted',0);

insert into G3E_PICKLIST( G3E_PNO, G3E_USERNAME, G3E_TABLE, G3E_KEYFIELD, G3E_VALUEFIELD, G3E_VALIDATION,G3E_PUBLISH) 
Values( 104,'POS_COUNT', 'REF_FSHELF', 'POSITIONS', 'POSITIONS', 'G3ERestricted',  0);

Notice that these picklists are created with equal values for G3E_KEYFIELD & G3E_VALUEFIELD, so GTech will show distinct values.
After creating this picklists, we create our edit menu with the following statements (there’s no need to use picklists on table G3E_ATTRIBUTE.G3E_PNO unless the picklist is part of a label)

INSERT INTO G3E_TABATTRIBUTE( G3E_TANO, G3E_ANO, G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600001, 601, 11,101,1, 0);

INSERT INTO G3E_TABATTRIBUTE( G3E_TANO, G3E_ANO, G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600002, 602, 11,102,2, 0);

INSERT INTO G3E_TABATTRIBUTE(G3E_TANO,G3E_ANO,G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600003, 603, 11,103,3, 0);

INSERT INTO G3E_TABATTRIBUTE(G3E_TANO,G3E_ANO,G3E_DTNO,G3E_PNO,G3E_ORDINAL,G3E_READONLY)
VALUES ( 600004, 604, 11,104,4, 0);

When you select a feature, GTech will show a Review-menu for the selected feature, so let’s copy our edit-menu to a review menu :

insert into g3e_tabattribute ( G3E_TANO, G3E_ANO, G3E_DTNO,G3E_ORDINAL,G3E_READONLY, G3E_DEFAULT)
select( G3E_TANO+40), g3e_ano,12,G3E_ORDINAL,1,G3E_DEFAULT from g3e_tabattribute;

Upon this point, these are the values a user sees when editing the feature in Feature Explorer :

As you can see, GTech shows the whole distinct set of values for the picklists. For attribute MANUFACTURER/ano=601 it shows all distinct values we have defined for picklists MANUFAC/pno=101, “KPE” & “Transmode”. For attribute MODEL/ano=601 it shows all distinct values for picklists MODEL_NAME/pno=102 “KP-CWDM/2”, “KP-CWDM/4”, “RMP-9003/01” & “RMP-9014/01” and so on. If you have a business rule saying selected values on this menu need to be a single row in table REF_FSHELF, we would have wanted only values “KP-CWDM/2” and “KP-CWDM/4″ for model because they are in the same row as MANUFACTUR=”KPE”. Since this is not the case, a user may very well be able to select a set of values not being a single row. That’s where fieldvaluefilters enter, you can use them to limit the set of choices a user can make to allow only values present in rows the user has selected before using a picklist. Fieldvalue filters have the following syntax :
#ANO=PL_FIELD
Which is saying limit the current picklist values to be only those where the current value for a given ano exists. So if we want to limit the possible choices for model to be only those who exist for the selected manufacturer, we can use the following sql-statement to update table G3E_TABATTRIBUTE:

UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '#601=MANUFAC' WHERE G3E_TANO = 600002;

After that, attributes SLOT_COUNT & POS_COUNT should also be dependent on previous choices :

UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '#601=MANUFAC&#602=MODEL_NAME' WHERE G3E_TANO = 600003;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '#601=MANUFAC&#602=MODEL_NAME&#603=SLOTS' WHERE G3E_TANO = 600004; 

After publishing metadata, you get the following results :

As you can see, the selection is filtered to the area indicated in red :

There’s one drawback with this approach though, these fieldvalue filters only work if the operator selects fields in the following order : MANUFACTURER, MODEL, SLOTS & POSITIONS. If a user first selects SLOTS, MODEL & MANUFACTURER have no values and the fieldvalue filters don’t work. To allow a user to select the attributes in random order, you need to extend the fieldvalue filters to accept null values and also to check all attributes making up a unique row:

UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#602=MODEL_NAME|#602=NULL)&(#603=SLOTS|#603=NULL)&(#604=POSITIONS|#604=NULL)' 		
WHERE G3E_TANO = 600001;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#601=MANUFAC|#601=NULL)&(#603=SLOTS|#603=NULL)&(#604=POSITIONS|#604=NULL)' 		
WHERE G3E_TANO = 600002;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#601=MANUFAC|#601=NULL)&(#602=MODEL_NAME|#602=NULL)&(#604=POSITIONS|#604=NULL)'	
WHERE G3E_TANO = 600003;
UPDATE G3E_TABATTRIBUTE SET G3E_FIELDVALUEFILTER = '(#601=MANUFAC|#601=NULL)&(#602=MODEL_NAME|#602=NULL)&(#603=SLOTS|#603=NULL)' 		
WHERE G3E_TANO = 600004;

Using these fieldvaluefilters, a user can select any attribute in a random order and GTech will show a distinct set of values if there are any previous selections. Putting it all together, our G3E_TABATTRIBUTE joined with G3E_ATTRIBUTE table now looks like this :

This is the query for the table above :

select	g3e_tabattribute.g3e_ano
    ,	g3e_attribute.g3e_field
    ,	g3e_tabattribute.g3e_pno    
    ,	g3e_tabattribute.g3e_fieldvaluefilter
    from g3e_tabattribute
    	inner join g3e_attribute on g3e_attribute.g3e_ano = g3e_tabattribute.g3e_ano 
     order by g3e_dtno, g3e_ordinal;

Summarized, fieldvalue filters can be of great help in validating user input, but can also be quite a hassle from a developers point of view to set up. It’s very error prone and little type mistakes can be very consuming to fix. But if all in place, they work just nice.

Hope this helps, Stephan