How to use in view while loop

(0) Share

Report

Posted on by waytod365

351

Hi all,

I have an one display method and now that method i need to move as computed column.

Below code: -

       WorkflowWorkItemTable workitem;
        str users;

        while select UserId from workitem
        where workitem.RefRecId == this.RecId
        && workitem.RefTableId == this.TableId
        && workitem.Status == WorkflowWorkItemStatus::Pending
        {
            users  = HcmWorker::find(HcmWorker::userId2Worker(workitem.UserId)).name()   ",";
        }
        return users;

Same code i want to select in computed column don't know how to do : - "," this kinda please give me example for that.

Thanks

I have the same question (0)

All responses (14)

Answers (1)

Suggested answer

Martin Dráb 237,967 Most Valuable Professional on at

Like (1)

Report

Your question is "How to use in view while loop", but that's wrong. You won't use any while loop in T-SQL that your computed column needs to generate.

Instead, look at STRING_AGG() function.

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

Thanks for your replay sir,

So how can i do STRING AGG to use in my view.

I have in the view three table

1. WorkflowWorkItemTable.

2. CustomTable (Relation has it in HcmWorker).

3. HcmWorker.

Now, the thing is how achieve same like as i mentioned above method sir. Would be great if you could provide example. then i note this kind of way.

Thanks

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (1)

Report

Please follow the link in my previous reply to see an example.

Then start writing T-SQL code that you want to generate. If you get stuck, show us code that you've written so far and explain your current problem.

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report
Thanks for your replay sir,

I'm stuck the STRING_AGG how to write within scope strfmt and that three table how to join in T-SQL.

Code;

public static server str getWorkflowPendingApprovalName() { DictView pendingNameView = new DictView(tableNum(WFPendingApprovalName)); str recid = SysComputedColumn::returnField(viewstr(WFPendingApprovalName), identifierStr(WorkflowWorkItemTable), fieldStr(WorkflowWorkItemTable, RefRecId)); str tableid = SysComputedColumn::returnField(viewstr(WFPendingApprovalName), identifierStr(WorkflowWorkItemTable), fieldStr(WorkflowWorkItemTable, RefTableId)); str status = SysComputedColumn::returnField(viewstr(WFPendingApprovalName), identifierStr(WorkflowWorkItemTable), fieldStr(WorkflowWorkItemTable, Status)); str expression = strfmt( @'SELECT STRING_AGG(name,",") FROM %1 HCMWorker ' 'WHERE HCMWorker.WORKER = %2 ' 'AND HCMWorker.DATAAREAID = %3 ' 'AND HCMWorker.PARTITION = %4 ' pendingNameView.name(), //users = HcmWorker::find(HcmWorker::userId2Worker(workitem.UserId)).name() ","; return ''; }

As i mentioned above code line 8 I'm stuck sir.

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report
Let's start with the design of your view. You mentioned that it contains three tables, including HcmWorker. Doesn't it mean that if there is work item with three workers, your view returns three rows? If so, where do you want to put the list of worker names? If you want your view returning a single record (with all worker names concatenated in a single field), then the structure of your view is wrong. You either need to changes this view, or you can use it as a data source for another view doing the aggregation.

Regarding your code, please start by compiling your code and fixing compilation errors.

Your code is trying to get a list of Name field values for a given worker, which looks wrong for several reasons:

HcmWorker table doesn't have any Name field.

You're finding HcmWorker by its Worker field, but such a field doesn't exist either.

There would be a single Name only, therefore trying to create a comma-separated list from it doesn't make a sense.

Please test your SQL code before writing X++ generating this code.

Also, look at what you put into expression variable - you'll immediately see a problem. You forgot to provide values for all the parameters (%1, %2, $3, $4).

Another problem is that you're returning an empty string. I assume you wanted to return expression.

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

Thanks sir,

Yes i want to all worker names concatenated in a single field so that i created one computed column.

Still, I'm stuck and i don't know how to do with in STRING_AGG in computed column please help me on this sir.

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (1)

Report

Could you please look at the first paragraph of my previous reply and answer my questions? Thank you.

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

Yes sir, i saw that blog for me its difficultly that STRING_AGG add in T-SQL in computed column that is why i asked give me a example.

Was this reply helpful? Yes No
Martin Dráb 237,967 Most Valuable Professional on at

Like (0)

Report

It's not about any blog. Let me repeat what I mean:

Let's start with the design of your view. You mentioned that it contains three tables, including HcmWorker. Doesn't it mean that if there is work item with three workers, your view returns three rows? If so, where do you want to put the list of worker names? If you want your view returning a single record (with all worker names concatenated in a single field), then the structure of your view is wrong. You either need to changes this view, or you can use it as a data source for another view doing the aggregation.

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

Sir, what's your point of view in it. if i need to that computed column field.

Etc: Field :- AssingedTo value :- should be (Jodi Christiansen,Charlie Carson,Ted Howard).

Forget about three dataSource. if i want to this kind of value what should i do in view on which dataSource i should i add that view. give me that your approach sir then i will follow that procedure.

Thanks for your attention sir.

Was this reply helpful? Yes No

Join the conversation

Community site session details

How to use in view while loop

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

How to use in view while loop

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates