Trying to fetch a Dynamic List using the following code:

                    fields = New ResultsetFields(7)
                    sortBy = New SortExpression

                    With fields
                        .DefineField(ActualEquipmentFailureFields.IDActualEquipmentFailure, 0, "IDActualEquipmentFailure")
                        .DefineField(ActualEquipmentFailureFields.Number, 1, "DefectNumber")
                        .DefineField(ActualEquipmentFailureFields.Description, 2, "Description")
                        .DefineField(ResourceFields.Name, 3, "Equipment")
                        .DefineField(ActualEquipmentFailureFields.DateReported, 4, "DateReported")
                        .DefineField(ActualEquipmentFailureFields.DateResolved, 5, "DateResolved")
                        .DefineField(EquipmentFailureTypeFields.Name, 6, "DefectType")
                    End With

                    With bucket.Relations
                        .Add(EntityClasses.ActualEquipmentFailureEntity.Relations.EquipmentFailureTypeEntityUsingIDEquipmentFailureType)
                        .Add(EntityClasses.ActualEquipmentFailureEntity.Relations.EquipmentEntityUsingIDEquipment)
                    End With

                    With sortBy
                        .Add(ActualEquipmentFailureFields.Number Or SortOperator.Ascending)
                    End With
--------------------

'call to FetchTypedList here

Equipment is a subtype of dbo.Scheduling.Resource

Here's the generated Sql:

SELECT DISTINCT 
  [LPA_L2].[IDActualEquipmentFailure], 
  [LPA_L1].[Number] AS [DefectNumber], 
  [LPA_L2].[Description], 
  [Scheduling].[Resource].[Name] AS [Equipment], 
  [LPA_L2].[DateReported], 
  [LPA_L2].[DateResolved], 
  [dbo].[EquipmentFailureType].[Name] AS [DefectType] 

FROM (((( 
[dbo].[Document] [LPA_L1]  

INNER JOIN [dbo].[ActualEquipmentFailure] [LPA_L2]  ON  [LPA_L1].[IDDocument]=  [LPA_L2].[IDActualEquipmentFailure]) 

INNER JOIN [dbo].[EquipmentFailureType]  ON  [dbo].[EquipmentFailureType].[IDEquipmentFailureType]=[LPA_L2].[IDEquipmentFailureType]) 

INNER JOIN [dbo].[Equipment] [LPA_L4]  ON  [LPA_L4].[IDEquipment]=[LPA_L2].[IDEquipment]) 

INNER JOIN [Scheduling].[Resource] [LPA_L3]  ON  [LPA_L3].[IDResource]=[LPA_L4].[IDEquipment]) 

WHERE ( ( ( 
[LPA_L2].[IDActualEquipmentFailure] IS NOT NULL))) 

ORDER BY [LPA_L1].[Number] ASC 

You'll notice that the FROM clause aliases [Scheduling].[Resource] to [LPA_L3], but the SELECT clause doesn't use it. Any help appreciated.

Jeff...

Hi Jeff,

I think you have a little mistake overthere

For example, at this line:

.DefineField(ResourceFields.Name, 3, "Equipment")

It's not clear to me if you intend to do this:

' the subtype Equipment, but I want to filter on the "Name" field of the supertype ResourceEntity
.DefineField(EquipmentFields.Name, 3, "Equipment")

Therefore, I don't get what this relation is intended for:

.Add(EntityClasses.ActualEquipmentFailureEntity.Relations.EquipmentEntityUsingIDEquipment)

as you aren't picking any field of the EquipmentEntity.

So, please try do the following (unless I misunderstood something):

.DefineField(EquipmentFields.Name, 3, "Equipment")

...

' remove this relation
' .Add(EntityClasses.ActualEquipmentFailureEntity.Relations.EquipmentEntityUsingIDEquipment)

I'm fairly sure this code worked in v1 (not positive, but pretty sure). The join you mentioned is there because it's required to get from ActualEquipmentFailure to Equipment which is a subtype of Resource - the DQE, I think, should be generating the extra join to get to Resource. Does that make sense?

The change to EquipmentFields.Name might help though. I'll check it out.

Edit Yep, that worked. Though I think it used to work as well, so I don't know if that's an issue. Thanks.

Jeff...

This works for you in v1, because your situation is lucky to work with it, others don't work, so we had to add code to make all work. The thing is that there are no aliases specified, so the framework has to guess what aliases there are. Therefore you have to specify in the filter fields in the type you also added to the list, otherwise it can't match them up.

This sounds weird, but consider fields from two subtypes of the same supertype and you filter on a field in the supertype. Which supertype version (as it is joined twice) is targeted?