Request to make the customer table (Customer (18)) fields: Balance (58, Decimal) and Balance Due (LCY) (67, Decimal) available for look up fields.
In our situation we want to be able to show the Credit Limit (LCY) (20, Decimal) (which is available for new fields) and the balance / balance due on sales orders before releasing them.
Those fields are FlowField, you can re-create them in other tables, as long as you have the needed filter fields.
Here are the FlowField definitions for the fields on the customer table:
field(58; Balance; Decimal)
{
AutoFormatExpression = “Currency Code”;
AutoFormatType = 1;
CalcFormula = Sum(“Detailed Cust. Ledg. Entry”.Amount WHERE(“Customer No.” = FIELD(“No.”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Balance’;
Editable = false;
FieldClass = FlowField;
}
field(59; “Balance (LCY)”; Decimal)
{
AutoFormatType = 1;
CalcFormula = Sum(“Detailed Cust. Ledg. Entry”.”Amount (LCY)” WHERE(“Customer No.” = FIELD(“No.”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Balance (LCY)’;
Editable = false;
FieldClass = FlowField;
}
field(60; “Net Change”; Decimal)
{
AutoFormatExpression = “Currency Code”;
AutoFormatType = 1;
CalcFormula = Sum(“Detailed Cust. Ledg. Entry”.Amount WHERE(“Customer No.” = FIELD(“No.”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Net Change’;
Editable = false;
FieldClass = FlowField;
}
field(61; “Net Change (LCY)”; Decimal)
{
AutoFormatType = 1;
CalcFormula = Sum(“Detailed Cust. Ledg. Entry”.”Amount (LCY)” WHERE(“Customer No.” = FIELD(“No.”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Net Change (LCY)’;
Editable = false;
FieldClass = FlowField;
}
field(62; “Sales (LCY)”; Decimal)
{
AutoFormatType = 1;
CalcFormula = Sum(“Cust. Ledger Entry”.”Sales (LCY)” WHERE(“Customer No.” = FIELD(“No.”),
“Global Dimension 1 Code” = FIELD(“Global Dimension 1 Filter”),
“Global Dimension 2 Code” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Sales (LCY)’;
Editable = false;
FieldClass = FlowField;
}
field(63; “Profit (LCY)”; Decimal)
{
AutoFormatType = 1;
CalcFormula = Sum(“Cust. Ledger Entry”.”Profit (LCY)” WHERE(“Customer No.” = FIELD(“No.”),
“Global Dimension 1 Code” = FIELD(“Global Dimension 1 Filter”),
“Global Dimension 2 Code” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Profit (LCY)’;
Editable = false;
FieldClass = FlowField;
}
field(64; “Inv. Discounts (LCY)”; Decimal)
{
AutoFormatType = 1;
CalcFormula = Sum(“Cust. Ledger Entry”.”Inv. Discount (LCY)” WHERE(“Customer No.” = FIELD(“No.”),
“Global Dimension 1 Code” = FIELD(“Global Dimension 1 Filter”),
“Global Dimension 2 Code” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Inv. Discounts (LCY)’;
Editable = false;
FieldClass = FlowField;
}
field(65; “Pmt. Discounts (LCY)”; Decimal)
{
AutoFormatType = 1;
CalcFormula = – Sum(“Detailed Cust. Ledg. Entry”.”Amount (LCY)” WHERE(“Customer No.” = FIELD(“No.”),
“Entry Type” = FILTER(“Payment Discount” .. “Payment Discount (VAT Adjustment)”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Pmt. Discounts (LCY)’;
Editable = false;
FieldClass = FlowField;
}
field(66; “Balance Due”; Decimal)
{
AutoFormatExpression = “Currency Code”;
AutoFormatType = 1;
CalcFormula = Sum(“Detailed Cust. Ledg. Entry”.Amount WHERE(“Customer No.” = FIELD(“No.”),
“Initial Entry Due Date” = FIELD(UPPERLIMIT(“Date Filter”)),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Balance Due’;
Editable = false;
FieldClass = FlowField;
}
field(67; “Balance Due (LCY)”; Decimal)
{
AutoFormatType = 1;
CalcFormula = Sum(“Detailed Cust. Ledg. Entry”.”Amount (LCY)” WHERE(“Customer No.” = FIELD(“No.”),
“Initial Entry Due Date” = FIELD(UPPERLIMIT(“Date Filter”)),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Balance Due (LCY)’;
Editable = false;
FieldClass = FlowField;
}
field(69; Payments; Decimal)
{
AutoFormatExpression = “Currency Code”;
AutoFormatType = 1;
CalcFormula = – Sum(“Detailed Cust. Ledg. Entry”.Amount WHERE(“Initial Document Type” = CONST(Payment),
“Entry Type” = CONST(“Initial Entry”),
“Customer No.” = FIELD(“No.”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Payments’;
Editable = false;
FieldClass = FlowField;
}
field(70; “Invoice Amounts”; Decimal)
{
AutoFormatExpression = “Currency Code”;
AutoFormatType = 1;
CalcFormula = Sum(“Detailed Cust. Ledg. Entry”.Amount WHERE(“Initial Document Type” = CONST(Invoice),
“Entry Type” = CONST(“Initial Entry”),
“Customer No.” = FIELD(“No.”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Invoice Amounts’;
Editable = false;
FieldClass = FlowField;
}
field(71; “Cr. Memo Amounts”; Decimal)
{
AutoFormatExpression = “Currency Code”;
AutoFormatType = 1;
CalcFormula = – Sum(“Detailed Cust. Ledg. Entry”.Amount WHERE(“Initial Document Type” = CONST(“Credit Memo”),
“Entry Type” = CONST(“Initial Entry”),
“Customer No.” = FIELD(“No.”),
“Initial Entry Global Dim. 1” = FIELD(“Global Dimension 1 Filter”),
“Initial Entry Global Dim. 2” = FIELD(“Global Dimension 2 Filter”),
“Posting Date” = FIELD(“Date Filter”),
“Currency Code” = FIELD(“Currency Filter”)));
Caption = ‘Cr. Memo Amounts’;
Editable = false;
FieldClass = FlowField;
}