6/19/2025 1:31:19 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Trouble with Dynamic Datagrid
Posted: 25 Jun 07 1:55 PM
|
I have a form that contains two data grids. The first displays financial securities - like 'products' (not oob products). Each security has a currency designation and can be split into several funds. The following SQL groups the securities by fund splits and rolls the amounts up. The first grid is generated with this SQL:
.SQL.Text="Select Sum(A1.Amount_Allocated) as amt , B1.Currency, C1.Fund " & _
"from sysdba.C_OPP_ASSETALLOCATION A1 " & _
"left Join sysdba.C_OPP_SECURITIES b1 " & _
"on a1.split_assetID = b1.C_Opp_SecuritiesID " & _
"left Join sysdba.Fund C1 " & _
"on A1.FUndID =C1.FundID " & _
"where a1.c_opportunitiesID = '" & sOppID &"' " & _
"group by C1.Fund, B1.Currency "
This works great - notice the SUM and GROUP by. Each record is designated a currency (USD, EUR, GBP etc) and grouped according to fund.
Next I need to remove amounts out of the recordset that are funded with USD and EUR - then take the totals of USD and EUR funds and subtract some other values from them to get a new USD and new EUR value to add to the second grid.
In the second grid, I would like to display the same data as the first grid, without any of the rows designated USD or EUR. I would then like to add two more rows to the grid containing a the new USD and EUR values.
I am very close, but I am receiving a 'Multi-step operation' error. I have narrowed this down to the Sum(A1.Amount_Allocated) as amtfunction in the SQL. If I take the sum and group by out of the sql the code works fine, although my funds are no longer rolled up. With the SUM function in the SQL, I get the error when it tries to run this line:
rs.Fields("amt").Value = txtNetEur.text .
Is there a way around this or another way to get data like this into a grid? Thanks
Sub PopulateGrid2(Sender)
Dim col
Dim i
Dim sOppID
sOppID=txtOppID.text
With dgNetProceeds2
.SQL.Text="Select Sum(A1.Amount_Allocated) as amt , B1.Currency " & _
"from sysdba.C_OPP_ASSETALLOCATION A1 " & _
"left Join sysdba.C_OPP_SECURITIES b1 " & _
"on a1.split_assetID = b1.C_Opp_SecuritiesID " & _
"where a1.c_opportunitiesID = '" & sOppID &"' and (B1.Currency<>'EUR' and B1.Currency<>'USD') " & _
"group by B1.Currency "
With .Columns
'remove any existing columns
If (.Count > 0) Then
For i = 0 To .Count - 1
.Item(0).Delete
Next
End If
Set col = .Add(0)
col.FieldName = "Currency"
col.Caption = "Currency"
col.ReadOnly = True
Set col = .Add(0)
col.FieldName = "amt"
col.Caption = "Amount Allocated"
col.ReadOnly = True
col.Width = 125
col.FormatType = ftFixed
col.FormatString = "%.2n"
' col.Width = 70
End With
'now refresh the grid to see the new columns
.Refresh
End With
AppendGrid(Sender)
End Sub
Sub AppendGrid(Sender)
dim rs
Set rs = dgNetProceeds2.Recordset
rs.addnew
rs.Fields("Currency").Value = "EUR"
rs.Fields("amt").Value = txtNetEur.text
Set dgNetProceeds2.Recordset = rs
set rs=nothing
dgNetProceeds2.refresh
End Sub |
|
|
|
Re: Trouble with Dynamic Datagrid
Posted: 25 Jun 07 3:10 PM
|
I'm having a little trouble understanding exactly how the three tables all relate to eachother. As I'm reading it, Allocations is a 1-to Many off of Opp, with Securities and Fund being 1 to manys off of Allocation. Is that correct? Could you provide definitions for each of the tables, so I can try to reproduce what you're doing?
Also, what version of SalesLogix are you working in? |
|
|
|
Re: Trouble with Dynamic Datagrid
Posted: 25 Jun 07 3:47 PM
|
I'll take a stab in the dark here. I want to say the problem is with amt. Because you're using the SQL sum function, I believe it makes the column read-only.
What about using the built in footer for summaries? Another alternative would be to do the sum function in code instead of through SQL. Lastly, you could perhaps use a hidden column and create a sum off that. For the existing columns you would merely push their sum into this hidden column since sum of a single record is always the same value. |
|
|
|
Re: Trouble with Dynamic Datagrid
Posted: 25 Jun 07 8:31 PM
|
Ok, I rethought a bit. I decided to add a column ( B1.Commited as fundamt2 ) to the sql and the grid, then update the column with either the USD, EUR or Amt from the hidden column.
This works great on a full refresh, but otherwise the grid is not binding. It is running this code without error, but grid is not populating (exept after full refresh), so any code refrering to the recordset or recordcount of the grid bombs (In AppendGrid sub).
I had put this code 'msgbox dgNetProceeds2.Recordset.recordcount' near the end of the sub and the code failed with a blank grid.
Question:
Why would this code populate the grid on shift F5, but not an F5 situation? The error is usually something like 'Object Required: dgNetProceeds2.recordset' when the offending code is uncommented
This sub is being called on AXFormChange.
SLX 7.01
Sub PopulateGrid2(Sender)
Dim col
Dim i
Dim sOppID
sOppID=txtOppID.text
With dgNetProceeds2
.SQL.Text="Select Sum(A1.Amount_Allocated) as amt , B1.Currency, B1.Commited as fundamt2 " & _
"from sysdba.C_OPP_ASSETALLOCATION A1 " & _
"left Join sysdba.C_OPP_SECURITIES b1 " & _
"on a1.split_assetID = b1.C_Opp_SecuritiesID " & _
"where a1.c_opportunitiesID = '" & sOppID &"' " & _
"group by B1.Currency, B1.Commited "
With .Columns
'remove any existing columns
If (.Count > 0) Then
For i = 0 To .Count - 1
.Item(0).Delete
Next
End If
Set col = .Add(0)
col.FieldName = "Currency"
col.Caption = "Currency"
col.ReadOnly = True
Set col = .Add(0)
col.FieldName = "amt"
col.Caption = "Amount Allocated"
col.ReadOnly = True
col.Width = 125
col.FormatType = ftFixed
col.FormatString = "%.2n"
col.visible=false
Set col = .Add(0)
col.FieldName = "fundamt2"
col.Caption = "Amount Allocated"
col.ReadOnly = True
col.FormatType = ftFixed
col.FormatString = "%.2n"
End With
'now refresh the grid to see the new columns
.Refresh
End With
dgNetProceeds2.refresh
'msgbox dgNetProceeds2.Recordset.recordcount
AppendGrid(Sender)
End Sub |
|
|
|
More Code
Posted: 26 Jun 07 7:11 AM
|
Here is the AppendGrid Sub. I get the error on this line: Select case rs(1) when an F5 refresh situation occurs. Seems to create the record set, but doesn't get anything from the grid because it doesn't populate. Again, runs fine on the full refresh. thx
Sub AppendGrid(Sender)
dim rs
Set rs = dgNetProceeds2.Recordset
If not dgNetProceeds2.Recordset.recordcount>0 then
rs.movelast
rs.movefirst
End If
do while not rs.eof
Select case rs(1) 'rs("currency")
Case "EUR"
' rs.delete
rs("fundamt2")=txtNetEur.text
Case "USD"
rs("fundamt2")=txtNetUSD.text
Case else
rs("fundamt2")=rs(0)
End Select
rs.movenext
loop
Set dgNetProceeds2.Recordset = rs
set rs=nothing
dgNetProceeds2.refresh
End Sub |
|
|
|
Re: More Code
Posted: 26 Jun 07 9:26 AM
|
Well, for the second grid, I would try moving the "End If" to the spot after dgNetProceeds2.Refresh (at the end of the sub), and then only have the Set RS = Nothing after that. This might help - I know grids can do some funny things on a refresh. It looks like it should work, but I've become superstitious with SalesLogix...  |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|