Small and medium business | Business Central, N...

Cue Table CalcFormula Options?

(1) Share

Report

Posted on by Greg Kujawa

627

Using BC 365 SaaS v23.3 here. Looking to extend a role dashboard. And part of that will involve creating a cue table and a cardpart page for displaying the logged-in user's associated records. Let's say we look at the base Microsoft Sales Cue table 9053.

Below is a table field for tallying up the open sales orders. Is it possible to do something like this, but to also filter those associated with just the logged-in user?

field(3; /Sales Orders - Open/; Integer)
{
AccessByPermission = TableData /Sales Shipment Header/ = R;
CalcFormula = count(/Sales Header/ where(/Document Type/ = const(Order),
Status = const(Open),
/Responsibility Center/ = field(/Responsibility Center Filter/)));
Caption = 'Sales Orders - Open';
Editable = false;
FieldClass = FlowField;
}

Categories:

Business Central

All responses (5)

Answers (0)

Greg Kujawa 627 on at

Like (1)

Report

Cue Table CalcFormula Options?

Thanks for the insightful feedback. Yes, I believe I'm going to forego using a FlowField and the underlying cue table. And build a cardpart page that has that field variable that is based on a local procedure that performs the lookup instead. I'm thinking that should fit the bill!
Suggested answer

YUN ZHU 73,698 Super User 2024 Season 2 on at

Like (2)

Report

Cue Table CalcFormula Options?

Hi, what this error means is that the function cannot be used in Filter.

Only filter conditions can be added here

You can first maintain User ID and SalesPers. data in User Setup, and then add it to Sales Header.

Another approach is not to use Flowfield.
Get the value in the database, and then use the OnDrilldown trigger or OnLookup trigger to drill into the record.

Hope this gives you some tips.

Thanks

ZHU
Greg Kujawa 627 on at

Like (0)

Report

Cue Table CalcFormula Options?

Here is what I have so far. Although VS Code is complaining that Reference 'Salesperson Code' in application object 'Sales Header' does not exist. Seems to be there when I look...lol. That error is apparently due to that filter calling the local procedure. When I substitute the local procedure call with a real salesperson code everything is fine.

/// <summary>

/// Table DCH Sales Orders Cue (ID 60010).

/// </summary>

table 60010 "DCH Sales Orders Cue"

{

DataClassification = ToBeClassified;

fields

{

field(1; PrimaryKey; Code[250])

{

DataClassification = ToBeClassified;

}

field(2; SalesInvoicesOpen; Integer)

{

FieldClass = FlowField;

CalcFormula = count("Sales Header" where("Document Type" = Filter(Order), Status = FILTER(Open), "Salesperson Code" = Filter(GetSalespersonCode)));

}

}

keys

{

key(PK; PrimaryKey)

{

Clustered = true;

}

}

local procedure GetSalespersonCode(): Code[20]

var

UserSetup: Record "User Setup";

UserId: Text;

Salesperson: Code[20];

begin

UserId := Database.UserId;

UserSetup.Reset();

UserSetup.SetFilter("User ID", '=%1', UserId);

if UserSetup.FindFirst() then

Salesperson := UserSetup."Salespers./Purch. Code";

exit(Salesperson);

end;

}
Greg Kujawa 627 on at

Like (0)

Report

Cue Table CalcFormula Options?

Yes, I realized that the header field contains the User ID, but how would that cross-reference the currently logged in user? Really what I'd like to do is cross-reference the Salesperson Code for any records that match the Salesperson Code that's associated with the currently logged in user. Since the User ID that was logged in to create the Sales Order might not be the Salesperson Code that's associated with the record.
gdrenteria 12,183 Most Valuable Professional on at

Like (1)

Report

Cue Table CalcFormula Options?

Hi

probably adding the header field filter "User ID".

Best regards

Gerardo