The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Problem with web Query
Posted: 26 Jan 07 12:05 PM
|
fiogf49gjkf0d I am trying to modify the Account Opportunity grid in the v7 web client. I have modified the system to associate opps to accounts and contacts through a third table called C_Acct_Association. I have done this so that if an account has a contact in the opp_contact table it will show in the grid, as well as the account that is associated with the opp. The table has accountID, contactID, OppID fields. The query below returns the correct opps in the lan client. I have tried a million ways to get the proper data to show up in a web query.
SELECT Distinct A1.OPPORTUNITYID, A1.DESCRIPTION, A1.STATUS, A1.ESTIMATEDCLOSE, " & _
"A1.SALESPOTENTIAL, A1.CLOSEPROBABILITY, A1.DAYSINPIPELINE, A1.DATEOPENED, A1.ACTUALCLOSE, " & _
"A1.CREATEDATE, A2.ACCOUNTID, A1.STAGE, A3.TechSEName FROM OPPORTUNITY A1, C_Acct_Association A2, C_Opportunity A3 " & _
"WHERE A2.ACCOUNTID = '" & application.basicFunctions.currentaccountID & "' " & _
"AND A1.OPPORTUNITYID=A2.OPPORTUNITYID and A1.OPPORTUNITYID=A3.OPPORTUNITYID
I am not sure what I am looking for, but any advice would be appreciated. thanks
|
|
|
|
Re: Problem with web Query
Posted: 27 Jan 07 11:11 PM
|
fiogf49gjkf0d i don't think the web handles "application.basicFunctions.currentaccountID". |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 7:17 AM
|
fiogf49gjkf0d Correct you are Carla. I was just trying to show what worked in the LAN client. It seems like it should be simple but is causing me trouble. |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 9:42 AM
|
fiogf49gjkf0d Hi Steve,
Have you tried running a trace or the Profiler to see what is being thrown to the backend?
Try this:
SELECT A1.OPPORTUNITYID, A1.DESCRIPTION, A1.STATUS,
A1.ESTIMATEDCLOSE, A1.SALESPOTENTIAL, A1.CLOSEPROBABILITY, A1.DAYSINPIPELINE, A1.DATEOPENED, A1.ACTUALCLOSE,
A1.CREATEDATE, A2.ACCOUNTID, A1.STAGE, A3.TechSEName
FROM OPPORTUNITY A1,
Left Join C_Acct_Association A2 ON A1.OPPORTUNITYID=A2.OPPORTUNITYID
Left Join C_Opportunity A3 ON A1.OPPORTUNITYID=A3.OPPORTUNITYID
WHERE A2.ACCOUNTID = 'myAccountID'
c |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 10:18 AM
|
fiogf49gjkf0d Sorry for the miscommunication - the query I posted works in the lan client. In the web client I don't have the option (I don't think) to write the SQL to get my results and still use the SLX grid functions. I have to use a slx web query or code my own html table which doesn't have sort functionablility.
Is there a way to choose what results I get in my slx web grid through SQL and not just a series of querybuilder queries?
Sorry if this is still unclear.. |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 10:31 AM
|
fiogf49gjkf0d Ah. OK, there is one thing that I am going to hazard. IF that query is generated (LAN) AND there were no joins; I would first recommend you use the Join manager to add joins for a more 'compact / efficient' handling of the query.
Here are a couple Troubleshooting ideas:
Are you using the " :myID " as the binding ID in the Web?
Do you have the ' myID ' as a field object in your HTML? (hidden or otherwise)
Are you sure the alias' are mapped to the correct fields (joins included)?
Have you run a trace / profiler to see what is being thrown to the backend?
|
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 11:26 AM
|
fiogf49gjkf0d Ok, got my query working. I had used an oob query and modified. I was finding that when I removed certain conditions nothing would display. Fiddle around again with success.
Now I have a duplicates problem (a step in the right direction, but still not just right..) . My custom table contains a record for every opp_contact. At the account level I only want to show based on accountID. In my lan query I use 'distinct' to take care of this. Using the SLX Web queries I don't see a way to indicate 'distinct'.. Any thoughts? Thanks
PS: Carla, Thanks for the advice on my lan query - not gonna mess with that now as it is working fine. I think the SLX query tool has actually forced me to write less efficient queries as many will not parse through the tool. |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 11:53 AM
|
fiogf49gjkf0d Distinct will work, but if you want it to always work, you will need to use Left/Right/Inner Joins. |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 12:02 PM
|
fiogf49gjkf0d I appreciate that Carla - my query is inefficient - got it, but again I don't have the opportunity to use sql in the queries entity (not the lan querybuilder, but the web queries (templates, aliases, actions and queries - those queries).
You have to use a nonintutive tool that doesn't allow for direct manipulation of SQL. Is there anyway to weed out dups with this tool?
My query is looking for c_acct_ass.accountID = :accid which works great after I stripped some other conditions out. (The web query uses a join manually created in the querybuilder - that one's for you Carla ). The problem is that in that same table c_acct_ass there is a record for each opp_contact. So if an opportunity has two contacts from the same account, the opp shows twice in my account_opp data grid. Thanks |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 12:05 PM
|
fiogf49gjkf0d Sorry, I should have been more clear. Multi-tasking...
If you add the joins in the join manager, that should clear the half the problem. The other half would be in the alias path created by traversing said joins. |
|
|
|
Re: Problem with web Query
Posted: 29 Jan 07 12:24 PM
|
fiogf49gjkf0d Although I am not entirely sure what to check, I will look into this - I really do appreciate the responses.
My custom table (c_acct_association) is a one to many off opporutnities, so there is Left Join SLX created. I added joins from Account.accountID and Contact.contactid to c_acct_association.accountID and c_acct_association.contactid respectively. Tried different types also with no luck.
Thanks for the advice. I'll keep trying.
|
|
|
|
Re: Problem with web Query
Posted: 31 Jan 07 10:23 AM
|
fiogf49gjkf0d Steve,
As you've found, there is no distinct option in the web client.
One way around it would be to build the datagrid using a #INCLUDE statment that generates the HTML manually and attach the datagrid behavior by putting
class="datagrid"
on the table that's supposed to be sortable. You'll probably also need to put class="cellGrid__pref" on the <td> tags as well. That way you can execute whatever SQL you want.
It'll probably take some work, but it can probably be done. It might not be worth the effort. A proof of concept on a simple table might be worthwhile.
The other option might be to put a subselect in your where clause by using the "in" condition in the query builder in web manager and for the condition put a SQL statement with your conditions - something like:
Select distinct accountid from account where...
To limit the actual results to the distinct.
Jeff
|
|
|
|
Re: Problem with web Query
Posted: 31 Jan 07 10:29 AM
|
fiogf49gjkf0d Thanks Jeff. Regarding the sql route - If I wanted to do something like 'select distinct accountID from c_account_association where accountid=:accid' would I use that as a literal (activate the checkbox in the query builder)? Is it possible to use that type of query in the query builder? Thanks |
|
|
|
Re: Problem with web Query
Posted: 31 Jan 07 10:37 AM
|
fiogf49gjkf0d Steve,
You would want to check "use value as literal" - that'll tell the web DLL to look for a value to substitute. I believe it will work - you may have to do a trace using SLXProfiler to see how it's interpreting the SQL. You might (or perhaps not, depends on how the DLL and/or provider builds the SQL) have to add some parens in your condition - the trace will probably tell what the provider is/is not putting in for you. I know I've done that sort of subquery before, but I can't be sure if it was in the LAN or web.
Jeff
|
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 2:38 AM
|
fiogf49gjkf0d Steve, To use the value as literal you could only put a value of :accid - it wouldnt work with a whole IN subquery. I'd do the <#INCLUDE> route and draw the grid yourself - you have so much more control of the data in it that way, and i find its a lot quicker performance wise than the dll drawn grids. |
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 10:56 AM
|
fiogf49gjkf0d Thanks Jeff and Nick. Am trying to create grid from an action. I have a few more questions:
1. How do I get an ID in the action from the template? In my case I need the accountID from the accopp template.
2. I am getting my results (with a hardcoded accid) and have them in a grid that looks close, but the sorting is not happening (although the sort arrows appear). Trying to reverse engineer the rendered source, but quite painful and possible impossible as all the code is executed). Here is my code thus far:
vTableHeader = "<table cellspacing=0 cellpadding=2 ONBEFORECOLSORT=" + Chr(34) + "resort()" + Chr(34) + " class=" + Chr(34)
vTableHeader = vTableHeader + "datagrid" + Chr(34) + " preformatted=" + Chr(34) + "TRUE" + Chr(34) + " xmlns:v=" + Chr(34) + "urn:schemas-microsoft-com:vml" + Chr(34) + ">"
retVal = vTableHeader
retVal = retVal + "<tr><td>Project #:</td><td>Stage:</td></tr>"
sFlag="dark"
For i = 1 to DBRecordCount(dbHandle)
if sFlag="dark" then
sTRclass="" + Chr(34) + "rowDark__pref" + Chr(34) + ""
else
sTRclass="" + Chr(34) + "rowLight__pref" + Chr(34) + ""
end If
retVal = retVal + "<tr class=" + sTRclass + ">"
retVal = retVal + "<td class='cellGrid__pref'>" & DBGetValue(dbHandle, "descr") & "</td>"
retVal = retVal + "<td class='cellGrid__pref'>" & DBGetValue(dbHandle, "stg") & "</td>"
retVal = retVal + "</tr>"
DBMoveTo dbHandle, "Next"
If sFlag="dark" then
sFlag="light"
else
sFlag="dark"
end if
Next
retVal = retVal + "</table>"
Would anyone who has accomplished this be willing to share a snip of unprocessed action code which properly formats the table? Can't hurt to ask, right?
Thanks
|
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 11:00 AM
|
fiogf49gjkf0d 1. RequestGetValue("accid")
2. I might be mistaken but i think you will need to post events back to the action to sort the SQL by which column they select.
|
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 12:56 PM
|
fiogf49gjkf0d Sorting is done by the datagrid behavior and it is done client side. You must configure the web query to have the ONBEFORECOLSORT markup with a value of "resort()". Just look at one of the stock product queries and you will see this in the layout properties of the table.
If you can not figure out how things seemed to be "wired up" client side it is often beneficial to peruse all the layout properties for the table, rows, and each column.
Hope this helps!
Timmus |
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 1:24 PM
|
fiogf49gjkf0d Steve already has this behaviour in his grid - and i too though this would have done it, however sorting appears to be done server side not client side - im guessing having the resort() somehow connects the standard slx grid to the db query. I have just double checked by doing a trace and sql is definately being 're-run' with the new order by statement attached, depending on which column header you select. Anyway its far too slow and 'refresh-y' to be client side.....
Thanks
Nick |
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 1:53 PM
|
fiogf49gjkf0d I am sorry for the poor communication. What I meant was that the code is not posted to a webaction for sorting in the normal web query world. The query is requested from the database server so it is a mix of client side and server side. I believe the call to the database server is handled via "non customizable" means.
Also note that I was responding to the sorting question without noticing that this is all generated with a web action. I suspect you are going to be out of luck. Since the webdll did not supply the datagrid via a webquery it likely has no idea how to re-request the data from the database with a modified sorting clause. Make sense?
I suppose you could post back to your original web template with a sorting clause (since you are handling the data retrieval). That just seems like a lot of reinvention to me.
I believe you can use a webquery.
1. Ensure your query has no joins to the tables that make it duplicate. If you need to display columns from these tables then you are out of luck.
2. Add a condition such as OpportunityID IN :sqlvalue and make the condition a literal
3. Generate the in clause client side in the acctabdata template. When the user clicks the opp tab pass the sqlvalue NVP to the template that hosts the web query. This provides the web query the subselect.
Note that I have done this for a customer with a nasty schema and it is in production working. Remember to use the escape function in your javascript when passing the sqlvalue nvp.
Note that "sqlvalue" has no meaning: its just what we use.
Timmus |
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 2:02 PM
|
fiogf49gjkf0d I thought you had 
Yes this way is much better actually pass your own sql to the grid and then enclose that in an in statement...that would get around the literal issue discussed previously... |
|
|
| |
| |
|
Re: Problem with web Query
Posted: 01 Feb 07 2:40 PM
|
fiogf49gjkf0d Timmus, I think I am with you, but need a little help in one place.
I have a query with this condition (returning dups)
Project.C_ACCT_ASSOCIATION.AccountID = :accid (as literal)
I should change to
Project.C_ACCT_ASSOCIATION.AccountID in :sqlvalue (as literal)
Got that. Then I should pass :sqlvalue from acctabdata in the querystring? like ...&sqlvalue=<select statement>? So the query will get :sqlvalue from the sqlvalue in the querystring?
I think this is getting clearer as I type.. Am I correct in my analysis above?
Thanks for the tip! |
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 2:44 PM
|
fiogf49gjkf0d CHANGED POST:
Ok, I am successfully passing the SQL Value in this line:
parent.querydata.location.href = "<#SYS name=swcpath>/view?name=" + adeftab + "&accid=<#AF name=accid>&conid="+conid+"&oppid=<#QF name=oppid>&atemplate=accmain&ctemplate=conmain&otemplate=oppmain&return=accmain&id=<#AF name=accid>&sqlvalue=select AccountID from C_ACCT_ASSOCIATION where accountID=<#AF name=accid>";
I changed my condtion in my query like so:
Opportunity.account.accountID in :sqlvalue
Getting a failed to parse query error in event viewer.. Am I on the right track?
Also, what is NVP? Thanks
|
|
|
|
Re: Problem with web Query
Posted: 01 Feb 07 4:07 PM
|
fiogf49gjkf0d It seems that you are on the right track.
NVP = name value pair = &accid=6JU900000001 where accid is the name and 6JU900000001 is the value.
I believe you need to include parens and ticks in your sql such as:
parent.querydata.location.href = "<#SYS name=swcpath>/view?name=" + adeftab + "&accid=<#AF name=accid>&conid="+conid+"&oppid=<#QF name=oppid>&atemplate=accmain&ctemplate=conmain&otemplate=oppmain&return=accmain&id=<#AF name=accid>&sqlvalue=(select AccountID from C_ACCT_ASSOCIATION where accountID='<#AF name=accid>')";
If the above does not work, try
parent.querydata.location.href = "<#SYS name=swcpath>/view?name=" + adeftab + "&accid=<#AF name=accid>&conid="+conid+"&oppid=<#QF name=oppid>&atemplate=accmain&ctemplate=conmain&otemplate=oppmain&return=accmain&id=<#AF name=accid>&sqlvalue=" + escape("(select AccountID from C_ACCT_ASSOCIATION where accountID='<#AF name=accid>')");
If you run a sql trace you should see the failing statement and determine the adjustments required.
And of course alerting the string you are trying to navigate to is always helpful.
Timmus |
|
|
|