FlowField filter from Source Table

MLoacher

Hello Everybody,

I want to create a Flow Field in the Sales Price Table which gives back the Cross reference no. only for Customers from the Item Cross Reference.
For this I user the following in CalcFormula:
"Lookup("Item Cross Reference"."Cross-Reference No." WHERE (Item No.=FIELD(Item No.),Variant Code=FIELD(Variant Code),Cross-Reference Type=FIELD(Sales Type),Cross-Reference Type No.=FIELD(Sales Code)))"

My Problem is now, that with the Table Filter "Cross-Reference Type=FIELD(Sales Type)" I get an empty value. I think this is because in the Sales Price Table the Sales Type option for customer is 0 and in the Item Cross Reference the option 0 is "" (nothing).

How can i manage to get the Item Cross Reference only for the Sales Type "Customer" in the Sales Price Table?

Best regards,
Max

kriki

Cross-Reference Type=FIELD(Sales Type) is not a good filter. "Cross-Reference Type" is not the same thing as "Sales Type".
You should use:
Cross-Reference Type=CONST(Customer).

RockWithNAV

Yes as Kriki suggested use Cross-Reference Type=CONST(Customer) instead of the filter you are using.

MLoacher

Thank you for your answers!

I tried this too, but the problem is that there is a Customer Price Groupe which has the same number as the Customer. So the Lookup shows the Item Reference from the Customer with the same number instead of nothing.

kriki

Rename the customer price group....

Or make a function in the Sales Price table that finds the cross reference number when needed and use that on the page instead of a flowfield. But it is not possible to filter or order on that field.