Association of Work Effort

[John Martin]

I'm trying to figure out the best way to integrate an application with the
Work Effort (WE) module.  I need to keep track of different tasks as events
occur in the application and believe that WE can do exactly what I need.
The question that I have is that I don't understand/see how a WE is
associated to an object (i.e. order, item, etc).  In my situation, we are
posting items to eBay in a scheduled job and if the listing fails, I want to
create a work effort to indicate that the item needs to be reviewed  and
relisted.  I would like to record the reason that the posting failed as
well.

I can see that the description would work for the failure reason but I don't
know how to associate the auction id to the WE.  Is the universalId or
sourceReferenceId the appropriate place to store references to other
entities?

Thanks,

John

[John Martin]

Noboby has posted a response to my original question so I guess just
thinking outloud.  Anyways I'm still looking for the correct solution.

I checked the code base and see no reference to the universalId being used.
It appears however that the sourceReferenceId is used in at least one case
to reference orderId so I'm assuming that might be what I should be doing?

Using sourceReferenceId though might not always be the best option since it
is not normalized therefore can only be associated to one other object.  In
addition, there is no way to indicate the type or purpose of the
relationship if one so chose.  The workEffortAttribute looks like it would
allow for an association but there is not key field (vchar 20) to do joins
on nor does it include a type or purpose attribute.

It appears that there was an attempt at a cross reference table for WE and
Orders being the orderHeaderWorkEffort but searching the code, it looks as
though only the createOrderHeaderWorkEffort service was created but not
used.  This doesn't appear to be being used.  This solution doesn't look to
me as being the best way since we don't want to have dozens of tables just
to relate WE to different tables.

Would it make sense to have a table (ie work_effort_reference) that could
relate WEs to other tables similar to work_effort_attribute?  I haven't
noticed any similar pattern in the data model and would be interested in
some feedback.  One concern with what I'm proposing is that using a generic
cross reference table would not support referential integrity.  This concern
however has already been breached by the
workEffort.sourceReferenceIdreferencing orders.

What I'm considering is a table:

    work_effort_reference

        work_effort_reference_id type="id-ne"
        reference_name type="id-long-ne"
        reference_id type="id-ne"
        last updated_stamp/tx_stamp
        created_stamp/tx_stamp

A purpose could be added but I don't have a requirement at this point.  One
could argue why not make the reference table completely generic and have
reference_name_to/from and reference_id_to/from and thereby allow any
associate between two tables to be persisted.

Any comments?

Thanks,

John


On 1/25/07, John Martin <[hidden email]> wrote:

>
> I'm trying to figure out the best way to integrate an application with the
> Work Effort (WE) module.  I need to keep track of different tasks as events
> occur in the application and believe that WE can do exactly what I need.
> The question that I have is that I don't understand/see how a WE is
> associated to an object ( i.e. order, item, etc).  In my situation, we are
> posting items to eBay in a scheduled job and if the listing fails, I want to
> create a work effort to indicate that the item needs to be reviewed  and
> relisted.  I would like to record the reason that the posting failed as
> well.
>
> I can see that the description would work for the failure reason but I
> don't know how to associate the auction id to the WE.  Is the universalId or
> sourceReferenceId the appropriate place to store references to other
> entities?
>
> Thanks,
>
> John
>

[David E Jones]

Which version/revision of OFBiz have you been reviewing?

I'd recommend starting OFBiz and looking at the Entity Reference  
pages in the WebTools webapp and look at WorkEffort entity and all of  
the relationships to it. You can also see it on the demo site hosted  
by Hotwax Media here:

https://demo.dejc.com:8443/webtools/control/main (login as admin:ofbiz)

For the most part we use "join" entities to link one top-level entity  
to another and use foreign keys and all rather than a general table  
like this as it really doesn't buy us much.

-David


On Jan 29, 2007, at 8:35 AM, John Martin wrote:

> Noboby has posted a response to my original question so I guess just
> thinking outloud.  Anyways I'm still looking for the correct solution.
>
> I checked the code base and see no reference to the universalId  
> being used.
> It appears however that the sourceReferenceId is used in at least  
> one case
> to reference orderId so I'm assuming that might be what I should be  
> doing?
>
> Using sourceReferenceId though might not always be the best option  
> since it
> is not normalized therefore can only be associated to one other  
> object.  In
> addition, there is no way to indicate the type or purpose of the
> relationship if one so chose.  The workEffortAttribute looks like  
> it would
> allow for an association but there is not key field (vchar 20) to  
> do joins
> on nor does it include a type or purpose attribute.
>
> It appears that there was an attempt at a cross reference table for  
> WE and
> Orders being the orderHeaderWorkEffort but searching the code, it  
> looks as
> though only the createOrderHeaderWorkEffort service was created but  
> not
> used.  This doesn't appear to be being used.  This solution doesn't  
> look to
> me as being the best way since we don't want to have dozens of  
> tables just
> to relate WE to different tables.
>
> Would it make sense to have a table (ie work_effort_reference) that  
> could
> relate WEs to other tables similar to work_effort_attribute?  I  
> haven't
> noticed any similar pattern in the data model and would be  
> interested in
> some feedback.  One concern with what I'm proposing is that using a  
> generic
> cross reference table would not support referential integrity.  
> This concern
> however has already been breached by the
> workEffort.sourceReferenceIdreferencing orders.
>
> What I'm considering is a table:
>
>    work_effort_reference
>
>        work_effort_reference_id type="id-ne"
>        reference_name type="id-long-ne"
>        reference_id type="id-ne"
>        last updated_stamp/tx_stamp
>        created_stamp/tx_stamp
>
> A purpose could be added but I don't have a requirement at this  
> point.  One
> could argue why not make the reference table completely generic and  
> have
> reference_name_to/from and reference_id_to/from and thereby allow any
> associate between two tables to be persisted.
>
> Any comments?
>
> Thanks,
>
> John
>
>
> On 1/25/07, John Martin <[hidden email]> wrote:
>>
>> I'm trying to figure out the best way to integrate an application  
>> with the
>> Work Effort (WE) module.  I need to keep track of different tasks  
>> as events
>> occur in the application and believe that WE can do exactly what I  
>> need.
>> The question that I have is that I don't understand/see how a WE is
>> associated to an object ( i.e. order, item, etc).  In my  
>> situation, we are
>> posting items to eBay in a scheduled job and if the listing fails,  
>> I want to
>> create a work effort to indicate that the item needs to be  
>> reviewed  and
>> relisted.  I would like to record the reason that the posting  
>> failed as
>> well.
>>
>> I can see that the description would work for the failure reason  
>> but I
>> don't know how to associate the auction id to the WE.  Is the  
>> universalId or
>> sourceReferenceId the appropriate place to store references to other
>> entities?
>>
>> Thanks,
>>
>> John
>>

[jonwimp]

John,

 > It appears that there was an attempt at a cross reference table for WE and
 > Orders being the orderHeaderWorkEffort but searching the code, it looks as
 > though only the createOrderHeaderWorkEffort service was created but not
 > used.  This doesn't appear to be being used.  This solution doesn't look to
 > me as being the best way since we don't want to have dozens of tables just
 > to relate WE to different tables.

I posted something on this before. Search the posts around early to mid Jan 07?

Entity OrderHeaderWorkEffort doesn't seem to used, I think. I left it at that because I was more
interested in WorkOrderItemFulfillment because I needed to link individual OrderItem(s) in an
Order to individual production runs.

I don't quite understand your generic reference table. Where do we specify which table is linked
to a WorkEffort record?

IMO, we shouldn't create such generic reference tables unless we're seeing an organized concerted
change in the framework to deal with something we might call "WorkEffort framework", where such a
framework would link any (any? really?) table rows with WorkEffort rows in a robust and
integrity-safe way. The whole idea sounds very open-ended, probably too big for me to wrap my mind
around.

Jonathon

John Martin wrote:

> Noboby has posted a response to my original question so I guess just
> thinking outloud.  Anyways I'm still looking for the correct solution.
>
> I checked the code base and see no reference to the universalId being used.
> It appears however that the sourceReferenceId is used in at least one case
> to reference orderId so I'm assuming that might be what I should be doing?
>
> Using sourceReferenceId though might not always be the best option since it
> is not normalized therefore can only be associated to one other object.  In
> addition, there is no way to indicate the type or purpose of the
> relationship if one so chose.  The workEffortAttribute looks like it would
> allow for an association but there is not key field (vchar 20) to do joins
> on nor does it include a type or purpose attribute.
>
> It appears that there was an attempt at a cross reference table for WE and
> Orders being the orderHeaderWorkEffort but searching the code, it looks as
> though only the createOrderHeaderWorkEffort service was created but not
> used.  This doesn't appear to be being used.  This solution doesn't look to
> me as being the best way since we don't want to have dozens of tables just
> to relate WE to different tables.
>
> Would it make sense to have a table (ie work_effort_reference) that could
> relate WEs to other tables similar to work_effort_attribute?  I haven't
> noticed any similar pattern in the data model and would be interested in
> some feedback.  One concern with what I'm proposing is that using a generic
> cross reference table would not support referential integrity.  This
> concern
> however has already been breached by the
> workEffort.sourceReferenceIdreferencing orders.
>
> What I'm considering is a table:
>
>    work_effort_reference
>
>        work_effort_reference_id type="id-ne"
>        reference_name type="id-long-ne"
>        reference_id type="id-ne"
>        last updated_stamp/tx_stamp
>        created_stamp/tx_stamp
>
> A purpose could be added but I don't have a requirement at this point.  One
> could argue why not make the reference table completely generic and have
> reference_name_to/from and reference_id_to/from and thereby allow any
> associate between two tables to be persisted.
>
> Any comments?
>
> Thanks,
>
> John
>
>
> On 1/25/07, John Martin <[hidden email]> wrote:
>>
>> I'm trying to figure out the best way to integrate an application with
>> the
>> Work Effort (WE) module.  I need to keep track of different tasks as
>> events
>> occur in the application and believe that WE can do exactly what I need.
>> The question that I have is that I don't understand/see how a WE is
>> associated to an object ( i.e. order, item, etc).  In my situation, we
>> are
>> posting items to eBay in a scheduled job and if the listing fails, I
>> want to
>> create a work effort to indicate that the item needs to be reviewed  and
>> relisted.  I would like to record the reason that the posting failed as
>> well.
>>
>> I can see that the description would work for the failure reason but I
>> don't know how to associate the auction id to the WE.  Is the
>> universalId or
>> sourceReferenceId the appropriate place to store references to other
>> entities?
>>
>> Thanks,
>>
>> John
>>
>
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.432 / Virus Database: 268.17.14/658 - Release Date: 1/29/2007 2:49 PM