Update field in multiple records based on a list or BAQ

system · August 23, 2011, 10:38am

Another idea would be to create a BAQ, then create a couple of shortcuts for the user.

First shortcut would be to run and export the BAQ as a csv file output (save as a process).
Next have a small batch file that moves the file from first step to Service Connect input folder or DMT tool (I have not used, but expect it could be used)

Service Connect and DMT seem to be good tools for this type of update - but may take some learning and you need to own the tools...

Of course, do the tracing options to see what table and methods are called in order to do your update correctly and try this in your TEST environment first to make sure other tables, fields are not adversely affected if they are related.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of cooner_55421
Sent: Monday, August 22, 2011 6:30 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Update field in multiple records based on a list or BAQ

Hi,

I'm looking for ideas on how to build something to find and update multiple records.

I don't have a version with updateable BAQs.

I'd like it to be something that an end user could use.
- Run a search/query to find the record(s).
- Then update a field on each of those records.
It would be the same table every time.
The same value is written every time too.

Thanks in advance for any suggestions.

[Non-text portions of this message have been removed]

system · August 22, 2011, 7:30pm

Hi,

I'm looking for ideas on how to build something to find and update multiple records.

I don't have a version with updateable BAQs.

I'd like it to be something that an end user could use.
- Run a search/query to find the record(s).
- Then update a field on each of those records.
It would be the same table every time.
The same value is written every time too.

Thanks in advance for any suggestions.

system · August 23, 2011, 8:48am

I did something like this for a UD field on the OrderDtl table.Â This was for 8.03.408A.
Â
I used a UD form but not the UD table itself.Â On the form I place an EpiUltraGrid.Â I place btnEpiCustom1 button on the form to retrieve the data from a BAQ.Â The code looks like this.
Â Private Sub btnEpiCustom1_Click(ByVal Sender As Object, ByVal Args As System.EventArgs) Handles btnEpiCustom1.Click
'// ** Place Event Handling Code Here **
otrans.pushstatustext("Retrieving Data ...", TRUE)
Dim dynQryAD As DynamicQueryAdapter = New DynamicQueryAdapter(UD08Form)
Dim baqID as String = "WI97087-SalesOrderBackLog"
Dim pcTable As String = "OrderHed"
Dim pcField As String = "CheckBox08"
Dim pbIsConst As Boolean = TRUE
Dim pcCondition As String = "="
Dim pcValue as String = FALSE
Dim pcToTable as String = String.Empty
Dim pcToField as String = String.Empty
Dim pcRightParentheses as String = String.Empty
Dim pcLeftParentheses as String = String.Empty
Dim pcAndOr as String = "AND"
Dim pbNeg as Boolean = False
dynQryAD.BOconnect()
dynQryAD.GetByID(baqID)
dynQryAD.AddWhereItem(dynQryAD.QueryDesignData, baqID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg)
dynQryAD.Update
'//copy all data from QueryDesignData tables to DynamicQueryData tables (only for tables that exist in both datasets)
Dim targetRow as DataRow
For each table as DataTable in dynQryAD.DynamicQueryData.Tables
If Table.Rows.Count = 0 Then
For Each sourceRow as DataRow in dynQryAD.QueryDesignData.Tables(table.ToString()).Rows
targetRow = table.NewRow()
targetRow.ItemArray = sourceRow.ItemArray
table.Rows.Add(targetRow)
Next
End If
Next
dynQryAD.Execute(dynQryAD.DynamicQueryData)
ugdEpiCustom1.DataSource = dynQryAd.QueryResults
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.CustID").Hidden = True
' ugdEpiCustom1.DisplayLayout.Bands(0).Columns("SpecialInstructions").Hidden = True
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Comments").Hidden = True
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.NeedByDate").Hidden = True
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("FutureNeedByDate").Hidden = True
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("BTonCreditHold").Hidden = True
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderHed.CheckBox08").Hidden = True
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.OrderLine").Hidden = True
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OpenValue").Format = "c"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OpenQty").CellAppearance.TextHAlign = 3
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OpenValue").CellAppearance.TextHAlign = 3
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.OrderNum").CellAppearance.TextHAlign = 3
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.ReqDate").Header.Caption = "Ship By"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.DoNotShipBeforeDate").Header.Caption = "Not Before"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.PartNum").Header.Caption = "Part"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.LineDesc").Header.Caption = "Desc"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.OrderNum").Header.Caption = "Order"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderHed.OrderHeld").Header.Caption = "OrdHold"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.CustID").Header.Caption = "Cust. ID"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.Name").Header.Caption = "Cust. Name"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("ShipVia.Description").Header.Caption = "ShipVia"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.Date01").Header.Caption = "Build Date"
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.Date01").Header.VisiblePosition = 2
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OpenQty").Header.VisiblePosition = 4
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.Name").Header.VisiblePosition = 10
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("ShipVia.Description").Header.VisiblePosition = 11
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("SpecialInstructions").Header.VisiblePosition = 13
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.ReqDate").Width = 75
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.DoNotShipBeforeDate").Width = 75
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.Date01").Width = 75
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.PartNum").Width = 130
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OpenQty").Width = 66
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.LineDesc").Width = 200
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.OrderNum").Width = 60
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderHed.OrderHeld").Width = 66
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("CreditHold").Width = 75
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.Name").Width = 190
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("ShipVia.Description").Width = 160
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.CustID").Width = 65
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Hold").Width = 56
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("SpecialInstructions").Width = 240
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("SpecialInstructions").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.ReqDate").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.DoNotShipBeforeDate").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.PartNum").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OpenQty").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.LineDesc").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OpenValue").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderRel.OrderNum").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderHed.OrderHeld").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("CreditHold").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.Name").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("ShipVia.Description").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Customer.CustID").CellActivation = Activation.NoEdit
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("Hold").CellActivation = Activation.NoEdit
If UD08Form.Session.UserID = "GAllen" OR UD08Form.Session.UserID = "Gotschall" OR UD08Form.Session.UserID = "manager" Then
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.Date01").CellActivation = Activation.AllowEdit
Else
ugdEpiCustom1.DisplayLayout.Bands(0).Columns("OrderDtl.Date01").CellActivation = Activation.NoEdit
End If
dynQryAD.Dispose
otrans.pushstatustext("Ready", FALSE)
If ugdEpiCustom1.DisplayLayout.Bands(0).SortedColumns.count = 0 Then btnEpiCustom4_Click(Sender, Args)
otrans.Update()
disableTools()
End Sub
Â Â
The field that can be edited is OrderDtl.Date01.Â The code to update the OrderDtl.Date01 is:
Â
Â Private Sub ugdEpiCustom1_AfterCellUpdate(ByVal Sender As Object, ByVal Args As Infragistics.Win.UltraWinGrid.CellEventArgs) Handles ugdEpiCustom1.AfterCellUpdate
Â Â '// ** Place Event Handling Code Here **
Â
Â Â Â Â Â Â Â If Args.Cell.Column.ToString() = "OrderDtl.Date01" Then
Â Â Â Â Â Â Â Â Â Â Â Dim adSalesOrderDtl as SalesOrdHedDtlAdapter = New SalesOrdHedDtlAdapter(UD08Form)
Â Â Â Â Â Â Â Â Â Â Â adSalesOrderDtl.BOconnect()
Â Â Â Â Â Â Â Â Â Â Â Dim dsSalesOrderDtl as SalesOrdHedDtlDataSet
Â Â Â Â Â Â Â Â Â Â Â Dim ReturnedData as Boolean
Â Â Â Â Â Â Â Â Â Â Â Dim FoundRows() as DataRow
Â Â Â Â Â Â Â Â Â Â Â adSalesOrderDtl.ClearData()
Â Â Â Â Â Â Â Â Â Â Â ReturnedData = adSalesOrderDtl.GetByID(Args.Cell.Row.Cells("OrderRel.OrderNum").Value)
Â Â Â Â Â Â Â Â Â Â Â FoundRows = adSalesOrderDtl.SalesOrdHedDtlData.OrderDtl.Select("OrderLine = " & Args.Cell.Row.Cells("OrderDtl.OrderLine").Value & " AND OrderNum = " & Args.Cell.Row.Cells("OrderRel.OrderNum").Value)
Â Â Â Â Â Â Â Â Â Â Â If FoundRows.GetUpperBound(0) = 0 Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â FoundRows(0).BeginEdit
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â FoundRows(0)("Date01") = Args.Cell.Value
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â FoundRows(0).EndEdit
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â adSalesOrderDtl.Update()
Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â Â Â Â adSalesOrderDtl.Dispose()
Â Â Â Â Â Â Â Â Â Â Â dsSalesOrderDtl = Nothing
Â Â Â Â Â Â Â End If
Â
Â End Sub
Â
There's a lot more to this, but this gives you the general idea.
Â
From: cooner_55421 <cooner_55421@...>
To: vantage@yahoogroups.com
Sent: Monday, August 22, 2011 7:30 PM
Subject: [Vantage] Update field in multiple records based on a list or BAQ

Â
Hi,

I'm looking for ideas on how to build something to find and update multiple records.

I don't have a version with updateable BAQs.

I'd like it to be something that an end user could use.
- Run a search/query to find the record(s).
- Then update a field on each of those records.
It would be the same table every time.
The same value is written every time too.

Thanks in advance for any suggestions.

[Non-text portions of this message have been removed]

system · August 23, 2011, 10:14am

Check out this blog entry I wrote a few weeks back, it may help give you a
starting point.
http://www.usdoingstuff.com/2011/06/08/mass-add-operations-to-jobs/

*Jose C Gomez*
*Software Engineer*
*
*
*checkout my new blog <http://www.usdoingstuff.com> *
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>

*Quis custodiet ipsos custodes?*

On Mon, Aug 22, 2011 at 7:30 PM, cooner_55421 <cooner_55421@...>wrote:

> **
>
>
> Hi,
>
> I'm looking for ideas on how to build something to find and update multiple
> records.
>
> I don't have a version with updateable BAQs.
>
> I'd like it to be something that an end user could use.
> - Run a search/query to find the record(s).
> - Then update a field on each of those records.
> It would be the same table every time.
> The same value is written every time too.
>
> Thanks in advance for any suggestions.
>
>
>

[Non-text portions of this message have been removed]