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

How to select only distinct records from a table?

(0) Share

Report

Posted on by AXBOY

625

Hi All,

I am quite new to NAV. I want to select distinct rows from a table?

Thanks in Advance.

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Saurav.Dhyani 14,419 User Group Leader on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Axboy,

Can you provide an example and for which version of NAV / BC?

Was this reply helpful? Yes No
AXBOY 625 on at

Like
a
(0)

Report
Copy link

Link copied!

It's for business Central.

I have to select all the records from JoB table whose type is resource and that too resource.typpe: Person. Then from these records I want only those records whose person name is unique.

Was this reply helpful? Yes No
Suggested answer

Saurav.Dhyani 14,419 User Group Leader on at

Like
a
(0)

Report
Copy link

Link copied!

I think you are referring table Resource not Job.

Below code sample will show those unique Resource Name -

LOCAL GetUniqueResources()

Resource.RESET;

Resource.SETCURRENTKEY("Name");

Resource.SETRANGE(Type,Resource.Type::Person);

IF Resource.FINDSET THEN REPEAT

IF PrevResourceName = Resource.Name THEN

MESSAGE('%1',Resource.Name);

PrevResourceName := Resource.Name;

UNTIL(Resource.NEXT=0);

I hope it helps.

Was this reply helpful? Yes No
AXBOY 625 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Saurav, thanks for the reply.

I have Job No. with me , and using that i want to build a string with Job.Description , of planning lines type=resource, resource.type=person with unique Job.No values. Can u pls help me with this?

Was this reply helpful? Yes No
Saurav.Dhyani 14,419 User Group Leader on at

Like
a
(0)

Report
Copy link

Link copied!

You need to share an example what you want before i can help you.

Was this reply helpful? Yes No
AXBOY 625 on at

Like
a
(0)

Report
Copy link

Link copied!

I am building a report where the user will select a Job Number and based on that number report will print data from the Job Planning lines related to that Job Number.

Now for one of the fields ,i have display a string in the form: JobPlanning.Description1,JobPlanning..Description2, JobPlanning.Description3.....etc , where JobPlanning.Type = Resource and that Resource.Type= Person.

I am able to achieve till here, but i want the String to have unique Description , that is i do not want that string to contain duplicate Person names. Hope you get my point.

Was this reply helpful? Yes No
Saurav.Dhyani 14,419 User Group Leader on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry, I still didnt understand.

I may need some sample data to understand requirment.

Was this reply helpful? Yes No
Suggested answer

Raju Gudipalli 185 on at

Like
a
(0)

Report
Copy link

Link copied!

Use code like this to eliminate the duplicates

Mydescription : text;

If strpos(mydescription,jobplanninglines.description) = 0 then

Mydescription += jobplanninglines.description.

Was this reply helpful? Yes No

Join the conversation

Community site session details

How to select only distinct records from a table?

Helpful resources

News and Announcements

Quick Links

Season of Sharing Community Challenge Launch!

Women in Power Builds Momentum

Congratulations to the May Top 10 Community Leaders

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

How to select only distinct records from a table?

Helpful resources

News and Announcements

Quick Links

Season of Sharing Community Challenge Launch!

Women in Power Builds Momentum

Congratulations to the May Top 10 Community Leaders

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates