For posterity, here's how we got it to work, running off a form event wizard (in our case, "on-click" of an ultra-combo where we select the child). It's kind of a mess as we finish cleaning it up, but it's good for directional purposes.
Dim edvJobMtl As EpiDataView = CType(oTrans.EpiDataViews("JobMtl"),EpiDataView)
Dim Key1 As String = edvJobMtl.dataView(edvJobMtl.Row)("ShortChar01")
Dim ChildKey1 As String = edvJobMtl.dataView(edvJobMtl.Row)("PartNum")
Dim adapterUD101 As UD101Adapter = New UD101Adapter(oTrans)
Dim MorePages As Boolean
adapterUD101.BOConnect()
Dim opts As New SearchOptions(SearchMode.AutoSearch)
opts.NamedSearch.WhereClauses.Add("UD101", String.Format("UD101.Key1 = '" & Key1 & "'", Key1))
opts.DataSetMode = DataSetMode.RowsDataSet
'Declare and Initialize Variables
'Call Adapter method
Dim dsUD101 As UD101DataSet = adapterUD101.GetRows(opts, MorePages)
FOR EACH ud101R as UD101DataSet.UD101Row in dsUD101.UD101 'Loop Through parent
epiBase.text = dsUD101.Tables(0).Rows(0)("Number01") 'store in temporary text field, though long term variable would be better
Next
'misc variables we need from UD parent/child
EstUnitCost = ctype(csm.GetNativeControlReference("dea0a06a-72a1-4d44-9855-7a575ba14579"),EpiCurrencyEditor)
Adder = ctype(csm.GetNativeControlReference("02d8b88e-ecab-4f8b-adfe-05fb53fce788"),EpiTextBox)
Base = ctype(csm.GetNativeControlReference("c1136576-e996-4bcd-8b20-6bf7859c44a4"),EpiTextBox)
'pull data from UD child
Dim i As integer = 0
FOR EACH ud101AR as UD101DataSet.UD101ARow in dsUD101.UD101A
if dsUD101.UD101A.Rows(i)("ChildKey1")=ChildKey1 then
epiAdder.text = dsUD101.UD101A.Rows(i)("Number02")
epiDescription.text = dsUD101.UD101A.Rows(i)("Character01")
end if
i = i + 1
Next
adapterUD101.Dispose()
adapterUD101 = nothing
'End populate adder price
'Set pricing
dim temp as decimal
temp = (Convert.ToDecimal(Adder.text) + Convert.ToDecimal(Base.text))
EstUnitCost.value = temp
EstUnitCost.Focus()
Ken
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Ken Williams
Sent: Wednesday, June 15, 2011 9:03 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] UD Parent/Child Tables
I meant basically an additional whereclause, something like "Key1 = '" & Key1 & "AND ChildKey1 ='" & ChildKey1 & "'"
The full picture is, the parent child contains a few commodities that have specific details (5 or 6 parents), under each parent will be 10-100 children with their own specific details. We're selecting both the parent and child from drop downs, once selected I need to pull only that parent & child info. The parent is working well perfect. The child is also working, with the loop, but I was hoping there'd be a simple way to limit the original data set. I tried the above whereclause, but it didn't limit the children.
Ken
-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Jose Gomez
Sent: Wednesday, June 15, 2011 8:51 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] UD Parent/Child Tables
You should only have Children on there related to the Parent.. Is your
parent going to have that many children?
In either case you can use something like LINQ to queryit out, but somewhere
in the .NET world it will have to do the looping. It shouldn't be too bad.
Just make sure you teach it to stop short (if you can)
Hope that helps.
*Jose C Gomez*
*Software Engineer*
*
*
*checkout my new blog <http://www.usdoingstuff.com> *
*
*T: 904.469.1524 mobile
E: jose@...<mailto:jose%40josecgomez.com>
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 Wed, Jun 15, 2011 at 10:46 AM, Ken Williams <
kwilliams@...<mailto:kwilliams%40intermountainelectronics.com>> wrote:
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
[Non-text portions of this message have been removed]
Dim edvJobMtl As EpiDataView = CType(oTrans.EpiDataViews("JobMtl"),EpiDataView)
Dim Key1 As String = edvJobMtl.dataView(edvJobMtl.Row)("ShortChar01")
Dim ChildKey1 As String = edvJobMtl.dataView(edvJobMtl.Row)("PartNum")
Dim adapterUD101 As UD101Adapter = New UD101Adapter(oTrans)
Dim MorePages As Boolean
adapterUD101.BOConnect()
Dim opts As New SearchOptions(SearchMode.AutoSearch)
opts.NamedSearch.WhereClauses.Add("UD101", String.Format("UD101.Key1 = '" & Key1 & "'", Key1))
opts.DataSetMode = DataSetMode.RowsDataSet
'Declare and Initialize Variables
'Call Adapter method
Dim dsUD101 As UD101DataSet = adapterUD101.GetRows(opts, MorePages)
FOR EACH ud101R as UD101DataSet.UD101Row in dsUD101.UD101 'Loop Through parent
epiBase.text = dsUD101.Tables(0).Rows(0)("Number01") 'store in temporary text field, though long term variable would be better
Next
'misc variables we need from UD parent/child
EstUnitCost = ctype(csm.GetNativeControlReference("dea0a06a-72a1-4d44-9855-7a575ba14579"),EpiCurrencyEditor)
Adder = ctype(csm.GetNativeControlReference("02d8b88e-ecab-4f8b-adfe-05fb53fce788"),EpiTextBox)
Base = ctype(csm.GetNativeControlReference("c1136576-e996-4bcd-8b20-6bf7859c44a4"),EpiTextBox)
'pull data from UD child
Dim i As integer = 0
FOR EACH ud101AR as UD101DataSet.UD101ARow in dsUD101.UD101A
if dsUD101.UD101A.Rows(i)("ChildKey1")=ChildKey1 then
epiAdder.text = dsUD101.UD101A.Rows(i)("Number02")
epiDescription.text = dsUD101.UD101A.Rows(i)("Character01")
end if
i = i + 1
Next
adapterUD101.Dispose()
adapterUD101 = nothing
'End populate adder price
'Set pricing
dim temp as decimal
temp = (Convert.ToDecimal(Adder.text) + Convert.ToDecimal(Base.text))
EstUnitCost.value = temp
EstUnitCost.Focus()
Ken
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Ken Williams
Sent: Wednesday, June 15, 2011 9:03 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] UD Parent/Child Tables
I meant basically an additional whereclause, something like "Key1 = '" & Key1 & "AND ChildKey1 ='" & ChildKey1 & "'"
The full picture is, the parent child contains a few commodities that have specific details (5 or 6 parents), under each parent will be 10-100 children with their own specific details. We're selecting both the parent and child from drop downs, once selected I need to pull only that parent & child info. The parent is working well perfect. The child is also working, with the loop, but I was hoping there'd be a simple way to limit the original data set. I tried the above whereclause, but it didn't limit the children.
Ken
-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Jose Gomez
Sent: Wednesday, June 15, 2011 8:51 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] UD Parent/Child Tables
You should only have Children on there related to the Parent.. Is your
parent going to have that many children?
In either case you can use something like LINQ to queryit out, but somewhere
in the .NET world it will have to do the looping. It shouldn't be too bad.
Just make sure you teach it to stop short (if you can)
Hope that helps.
*Jose C Gomez*
*Software Engineer*
*
*
*checkout my new blog <http://www.usdoingstuff.com> *
*
*T: 904.469.1524 mobile
E: jose@...<mailto:jose%40josecgomez.com>
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 Wed, Jun 15, 2011 at 10:46 AM, Ken Williams <
kwilliams@...<mailto:kwilliams%40intermountainelectronics.com>> wrote:
>[Non-text portions of this message have been removed]
>
> Jose, thanks for the help. We had to tinker a bit with it, but this got us
> past our last hurdle. On to the finished product!
>
> One question, when you're looping through the children rows, is there a
> simple way to filter based on childkeys? What I've got now is a simple
> counter to check if Row(i)("ChildKey1") = ChildKey1, which works - but I'm
> concerned when our child table gets to 100+ rows it may cause performance
> issues as it loops through trying to find the right record.
>
> Ken
>
>
> -----Original Message-----
> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf
> Of Jose Gomez
> Sent: Wednesday, June 15, 2011 6:38 AM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> Subject: Re: [Vantage] UD Parent/Child Tables
>
> There usually no adapter for searching child tables directly, try using the
> GetRows method of the parent adapter passing it a where clause for the
> parent and child (if you need one).
>
> UD101 and UD101A are related so when you get the UD101 record normally you
> get the children with it too so unless you need specific children getting
> the parent should be enough.
> Try something like this
>
> 'Declare and Initialize EpiDataView Variables
> 'Create instance of Adapter
> Dim adapterUD101 As UD101Adapter = New UD101Adapter(oTrans)
> Dim MorePages As Boolean
> Dim Key1, Key2 as String
> adapterUD101.BOConnect()
> Dim opts As New SearchOptions(SearchMode.AutoSearch)
> opts.NamedSearch.WhereClauses.Add("UD100", String.Format("UD100.Key1 =
> '{0}'
> AND UD100.Key1 = '{0}'", Key1,Key2))
> opts.DataSetMode = DataSetMode.RowsDataSet
> 'Declare and Initialize Variables
> 'Call Adapter method
> Dim dsUD101 As UD101DataSet = adapterUD101.GetRows(opts, MorePages)
> FOR EACH ud101R as UD101DataSet.UD101Row in dsUD101.UD101 'Loop Through
> parent
> 'Do Something (bind to Drop Down)
> Next
> FOR EACH ud101AR as UD101DataSet.UD101ARow in dsUD101.UD101A
> 'Do Something w. Children
> Next
> 'Cleanup Adapter Reference
> adapterUD101.Dispose()
> adapterUD101 = Nothing
>
> *Jose C Gomez*
> *Software Engineer*
> *
> *
> *checkout my new blog <http://www.usdoingstuff.com> *
> *
> *T: 904.469.1524 mobile
>
> E: jose@...<mailto:jose%40josecgomez.com>
> 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 Tue, Jun 14, 2011 at 10:22 PM, Ken Williams <
> kwilliams@...<mailto:kwilliams%40intermountainelectronics.com>> wrote:
>
> >
> >
> > We're trying to fully utilize a UD parent/child table (UD101/UD101A for
> > example) to populate data into Job Material. We've succeeded in linking
> the
> > UD tables, pulling data from the UD parent (UD101) and even populating a
> > combo box with something from the child (UD101A). The one thing we can't
> do,
> > is pull in additional information from the UD101A table. This should be
> > simple, but I am unable to find the correct search adapter and/or
> > whereclause to succeed.
> >
> > Begging for help here, this is the last piece of a pretty elaborate
> change
> > that will drastically reduce a lot of time and headaches for many people
> in
> > our company.
> >
> > Thanks in advance to anyone who can help!
> >
> > Here's a copy of the code we have and some snippets of what I've tried to
> > get the UD101A to work:
> >
> > Private ud101Adapter as UD101Adapter
> >
> > Sub InitializeCustomCode()
> > AddHandler ucbChild.BeforeDropDown, AddressOf
> > Script.ucbChild_BeforeDropDown
> > ud101Adapter = New UD101Adapter(oTrans)
> > ud101Adapter.BOConnect()
> > End Sub
> >
> > Sub DestroyCustomCode()
> > RemoveHandler ucbChild.BeforeDropDown, AddressOf
> > Script.ucbChild_BeforeDropDown
> > ud101Adapter.Dispose()
> > ud101Adapter = Nothing
> > End Sub
> >
> > Private Sub JobEntryForm_Load(ByVal sender As Object, ByVal args As
> > EventArgs)
> > 'Add Event Handler Code
> > 'This populates the child (UD101A) combo box with the related data from
> the
> > parent
> > ucbChild.ValueMember = "ChildKey1"
> > ucbChild.DataSource = ud101Adapter.UD101Data.UD101A
> > ucbChild.DisplayMember = "ChildKey1"
> > Dim fields As String() = New String() {"ChildKey1"}
> > ucbChild.SetColumnFilter(fields)
> > FillUD101ACombo("")
> >
> > End Sub
> >
> > Private Sub ucbChild_BeforeDropDown(ByVal sender As Object, ByVal args As
> > System.ComponentModel.CancelEventArgs)
> > ' ** Place Event Handling Code Here **
> > 'this also populates the child(UD101A) combo box
> > Dim edvJobMtl As EpiDataView =
> > CType(oTrans.EpiDataViews("JobMtl"),EpiDataView)
> > Dim Key1 As String = edvJobMtl.dataView(edvJobMtl.Row)("ShortChar01")
> > 'where we've stored the UD101.Key1 field, will likely switch to a direct
> > pull later
> > 'MessageBox.show(Key1)
> > FillUD101ACombo(Key1)
> >
> > End Sub
> >
> > Private Sub FillUD101ACombo(Key1 As String)
> > 'last piece required to populate child(UD101A) combo box
> > Dim myHash As System.Collections.Hashtable = New
> > System.Collections.Hashtable()
> > Dim whereClause As String = String.Empty
> > If Not (String.IsNullOrEmpty(Key1)) Then
> > whereClause = "Key1 ='" & Key1 & "'"
> > End If
> >
> > myHash.Add("UD101A", whereClause)
> > Dim opts As SearchOptions =
> >
> Epicor.Mfg.UI.Searches.SearchOptions.CreateRuntimeSearch(myHash,DataSetMode.RowsDataSet)
> > ud101Adapter.InvokeSearch(opts)
> >
> > End Sub
> >
> > Private Sub epiButtonC1_Click(ByVal sender As Object, ByVal args As
> > System.EventArgs)
> > ' ** Place Event Handling Code Here **
> > 'currently firing on button click, will modify to proper form event later
> >
> > 'Populate Base Price from UD Parent (UD101), this works perfect
> > dim recSelected as boolean
> > Dim edvJobMtl As EpiDataView =
> > CType(oTrans.EpiDataViews("JobMtl"),EpiDataView)
> > Dim Key1 As String = edvJobMtl.dataView(edvJobMtl.Row)("ShortChar01")
> > dim whereClause as string ="Key1 ='" & Key1 & "'"
> > Dim dsUD101 as DataSet =
> >
> Epicor.Mfg.UI.FormFunctions.Searchfunctions.listlookup(oTrans,"UD101Adapter",recSelected,False,whereClause)
> > epiBase.text = dsUD101.Tables(0).Rows(0)("Number01")
> > 'End populate base price
> >
> > 'Populate adder price from UD child (UD101A), this is where we're stuck,
> > lots of examples below mostly remarked out as we've discarded along the
> way
> > 'Dim ChildKey1 As String =
> edvJobMtl.dataView(edvJobMtl.Row)("Character01")
> > 'dim whereClause2 as string ="ChildKey1 ='" & ChildKey1 & "'"
> > 'Dim dsUD101a as DataSet =
> >
> Epicor.Mfg.UI.FormFunctions.Searchfunctions.listlookup(oTrans,"UD101Adapter",recSelected,False,whereClause2)
> > epiAdder.text=ud101Adapter.UD101Data.Tables("UD101A").Rows(0)("Number02")
> > 'epiAdder.text = dsUD101a.Tables(0).Rows(0)("Number02")
> > 'End populate adder price
> >
> > End Sub
> >
> > Ken
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
> already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder and
> Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/>
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
>
>
>
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
[Non-text portions of this message have been removed]