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
Eliud,
I think Shan's suggestion to create custom case statements is the way to go for this.
Regards,
Kirk
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.
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.
Yes, definitely ... what is the question :)
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (