Recreate Custom Smart List

(0) Share

Report

Posted on by Eliud

130

We have a web application we are creating to work with GP. As part of it we are creating a set of Smart Lists . our application is able to query GP to get the custom smart list and using the data in the database recreate the smart list SQL which it then users to get data from GP.

Please note this is how GP processes Smart lists based on results of running the SQL profiler. The smart lists are created using smart list designer.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sort by

KirkLivermont 5,985 on at

Like (0)

Report
Copy link

Link copied!

Eliud,

I am not sure I understand your question. Could you elaborate on what you are trying to accomplish and where you are having trouble?

Regards,

Kirk

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report
Copy link

Link copied!

Yes, definitely ... what is the question :)

Was this reply helpful? Yes No
Eliud 130 on at

Like (0)

Report
Copy link

Link copied!

sorry guys, I did not copy the entire story across...

We have a web application we are creating to work with GP. As part of it we are creating a set of Smart Lists . our application is able to query GP to get the custom smart list and using the data in the database recreate the smart list SQL which it then users to get data from GP.

Please note this is how GP processes Smart lists based on results of running the SQL profiler. The smart lists are created using smart list designer.

I have identified the tables that are used to store the information about a smart query and can recreate the SQL statement however when dealing with drop down lists GP calls a function so as to get the text "user friendly" definition of the value e.g. if you create a smart query that returns the field SRVRECTYPE "Service Record Type" as used by the table SVC00202 you find that the SQL created by smart list designer has

TWO.dbo.FS_FUNC_Service_Record_Type([SVC00202].SRVRECTYPE) AS 'Service Record Type',

that fuction returns the value of the smart record type e.g. Invoice, Quote etc while the database stores a small int value.

When i recreate my SQL in my app i have no way of knowing which function to call, i can identify a drop down list but what function to call is a mystery. how does GP decide what func to call. i have used SQL profiler to see what gets called but i have seen any answers.

I have found a table called DYN01.dbo.SY10997 which seems useful but i need a Field_ResID value e.g. 22004 shows be the function to call for 'Service Record Type'. I cannot find how the Field_ResID is derived.

Does anyone have an idea how this is done or a easier way to call GP smart list from an external application which then means i do not have to worry about how the smart list is defined. I only care about the data.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi,

One way of achieving is You can write some custom case statements in SQL query to get the desired value. In order to derive your requirement you should be referring the Dynamics GP SDK which contains all the tables and its field references. I would also refer to Victoria blog where she has explained all the module table details and its references.

victoriayudin.com/.../

Was this reply helpful? Yes No
KirkLivermont 5,985 on at

Like (0)

Report
Copy link

Link copied!

Eliud,

I think Shan's suggestion to create custom case statements is the way to go for this.

Regards,

Kirk

Was this reply helpful? Yes No