Skip to main content
Business Central forum
Suggested answer

Cue Table CalcFormula Options?

editSubscribe (1) ShareShare
ReportReport
Posted on by 543
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:
  • Greg Kujawa Profile Picture
    Greg Kujawa 543 on at
    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 Profile Picture
    YUN ZHU 59,926 Super User on at
    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 Profile Picture
    Greg Kujawa 543 on at
    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 Profile Picture
    Greg Kujawa 543 on at
    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 Profile Picture
    gdrenteria 2,706 Super User on at
    Cue Table CalcFormula Options?
    Hi
    probably adding the header field filter "User ID".
    Best regards
    Gerardo

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,045 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,570 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans