ValuelistItems problem

[desta]

Seems basic, but I'm stuck with a simple issue here:

 

To simplify the problem, suppose I have One table "Invoices" and related table "Items". The Items of an invoice can be shipped on more than one transport, so the latter has a textfield "transportNo" whose values are stored in a valuelist that uses values from the field, so that each time a "transportNo" is added in the job, it is available in the listbox for subsequent added items.

 

Now, for some reasons, I'd rather not create a separate table for transports... but I need to know how many different transportNo have been inserted in the textfield, for every job. Unfortunately, Both count summary field or "ValueCount (List(TransportNo)" calculation field will return the count of items instead of count of distinct "TransportNo", even if many different items have the same transportNo. So I managed to replace it with the following calculation field:

 

CalculationField = ValueCount ( ValueListItems ( "Filename"; "TransportsList" ) )

 

It succeds in calculating the count of distinct "TransportNo" in the related table, but it seems not to recalculate adequately... when I change one records's "TransportNo" for a new one, the calculation still returns the same old value, no matter if I store the calculation field or leave it unstored..... yet the valuelist updates correctly, and so does the listbox. It seems to update when I "Manage Database" fields though....

 

Why? Is There another way to achieve this?

 

Note that items can have no transpotNo. In that case, the TransportNo is left blank, and must not be counted as one.

[desta]

....I Know I could put the Transports in a related table and it would work, but what happens when an invoice is duplicated, and from the duplicated value, some items that have TransportNo assigned are deleted? ...If I delete automatically related values, then does the firs invoice still have its TransportNo in the table? Worth a try...

[desta]

Ahhh, got it. The relationship was set through a global field that shouldn't have been global. Now it works fine. Oh! so many time lost searching!

 

And by the way I also found out on this site that you can also do so with a custom function: "CustomFunction(values)" that goes like this:

 

 

Let([

l = values;

v= GetValue(l; 1);

newL = RightValues( l ; ValueCount( l ) - 1)

] ;

Case(

  l = "" ;"" ; 

  If(    ValueCount(FilterValues(l; v)) > 1 ;  "" ; v & "¶" ) & UniqueValues( newL ) 

 )

)

 

Interesting...