ExternalId support in relevant entities

[Suraj Khurana-2]

Hello team,

As far as my understanding, externalId in OrderHeader is used to save
orderId reference of any other system into our system.

If this is the only case, we should also maintain something similar on
following entities as well as consistency and they will be in need in case
of a full-fledged integration with OFBiz environment with any other system:

   1. ReturnHeader
   2. ContactMech
   3. Party (already exist)
   4. Facility
   5. Product (can be discussed, Identification type SKU can also do the
   job)

Please let me know your thoughts on this.

--
Best Regards,
Suraj Khurana
Technical Consultant

[Pawan Verma]

Nice enhancement +1,

I would like to add Shipment entity into the list.

--
Kind Regards,
*Pawan Verma* | Technical Consultant
HotWax Systems <http://www.hotwaxsystems.com/>
Plot no. 80, Scheme no. 78 Part 2, Near Brilliant Convention Center, Indore,
M.P. 452010
Linkedin: *Pawan Verma <https://www.linkedin.com/in/pawan--verma>*


On Thu, May 2, 2019 at 5:14 PM Suraj Khurana <[hidden email]>
wrote:

> Hello team,
>
> As far as my understanding, externalId in OrderHeader is used to save
> orderId reference of any other system into our system.
>
> If this is the only case, we should also maintain something similar on
> following entities as well as consistency and they will be in need in case
> of a full-fledged integration with OFBiz environment with any other system:
>
>    1. ReturnHeader
>    2. ContactMech
>    3. Party (already exist)
>    4. Facility
>    5. Product (can be discussed, Identification type SKU can also do the
>    job)
>
> Please let me know your thoughts on this.
>
> --
> Best Regards,
> Suraj Khurana
> Technical Consultant
>

[Pierre Smits-3]

Current methodology of having the externalId field in the various tables is
limiting the capabilities of OFBiz. With this an object can have only 1
externalId value. However, it is quite feasible that an object can be
associated with various external systems with each having a different
externalId value. This is particularly true for the party entity.

I wonder whether this is necessary for a facility. If a supplier has an Id
value for one of the internally used facilities, why would the adopter
care? Similar thoughts are on contact mech and shipment. But a good example
and explanation for each may help.

The external Id of a product is (as far as it is related to a product
supplied by a 3rd party) captured in SupplierProduct entity. If the intent
is to capture the product Id as it is used by a customer and other parties,
then the same reasoning as used for parties (see above) applies. Why would
an OFBiz adopter care what the external Ids of downstream parties are?

The identification type SKU can't be used for this purpose, as it is
intended to have a value based on internally used variation/feature
combinations. I suggest reading up on [1]. The definitions used by either
supplier, customer, etc. may lead to conflicts.


[1] https://en.wikipedia.org/wiki/Stock_keeping_unit

Best regards,

Pierre Smits

*Apache Trafodion <https://trafodion.apache.org>, Vice President*
*Apache Directory <https://directory.apache.org>, PMC Member*
Apache Incubator <https://incubator.apache.org>, committer
*Apache OFBiz <https://ofbiz.apache.org>, contributor (without privileges)
since 2008*
Apache Steve <https://steve.apache.org>, committer


On Thu, May 2, 2019 at 1:44 PM Suraj Khurana <[hidden email]>
wrote:

> Hello team,
>
> As far as my understanding, externalId in OrderHeader is used to save
> orderId reference of any other system into our system.
>
> If this is the only case, we should also maintain something similar on
> following entities as well as consistency and they will be in need in case
> of a full-fledged integration with OFBiz environment with any other system:
>
>    1. ReturnHeader
>    2. ContactMech
>    3. Party (already exist)
>    4. Facility
>    5. Product (can be discussed, Identification type SKU can also do the
>    job)
>
> Please let me know your thoughts on this.
>
> --
> Best Regards,
> Suraj Khurana
> Technical Consultant
>

[Scott Gray-3]

I'd tend to agree with Pierre here, following the {entity}Identification
pattern is probably a better approach long term simply because the
externalId pattern breaks as soon as you need more than one identifier.  If
the likelihood of multiple IDs is low, then the {entity}Attribute pattern
might be a better approach.

But with that said, when customizing a system I'll typically just throw an
additional field on the entity and be done with it.  It doesn't take long
to write a helper method get{Entity}ByExternalId(String) to hook it up.
Because there's very little business logic that OFBiz can attach to these
fields, the amount of time we can save developers by having these fields
available in advance is very small.  That changes with the Identification
pattern because we can provide from/thru dating, enforce unique constraints
and regex patterns etc. which will save developers more time.

Regarding Facility, it's useful to have an external identifier when you're
integrating/syncing with a 3PL system or in general if you don't own the
facility that you're using.  But that could be said of almost any table in
the system when you need to keep it in sync with another.

I almost wonder if a generic entity (EntityIdentification) would be a
better approach that contains something like:
- entityName
- entityId
- fromDate
- thruDate
- idType
- value
We could then provide a set of generic helper methods/services to perform
lookups and update values e.g. GenericValue facility =
getEntityById("Facility", "3PL", "123").  The CRUD services could use
another table (EntityIdentificationType) to help with enforcing contraints:
- entityName
- idType
- requireUnique
- validationRegex

The main downsides would be:
- Duplication with the current <entity>Identification pattern (confusion)
- Lack of foreign keys back to the entities being identified
- Largely unused pattern in general currently (I think only EntityAuditLog
is similar)

Regards
Scott

On Fri, 3 May 2019 at 00:33, Pierre Smits <[hidden email]> wrote:

> Current methodology of having the externalId field in the various tables is
> limiting the capabilities of OFBiz. With this an object can have only 1
> externalId value. However, it is quite feasible that an object can be
> associated with various external systems with each having a different
> externalId value. This is particularly true for the party entity.
>
> I wonder whether this is necessary for a facility. If a supplier has an Id
> value for one of the internally used facilities, why would the adopter
> care? Similar thoughts are on contact mech and shipment. But a good example
> and explanation for each may help.
>
> The external Id of a product is (as far as it is related to a product
> supplied by a 3rd party) captured in SupplierProduct entity. If the intent
> is to capture the product Id as it is used by a customer and other parties,
> then the same reasoning as used for parties (see above) applies. Why would
> an OFBiz adopter care what the external Ids of downstream parties are?
>
> The identification type SKU can't be used for this purpose, as it is
> intended to have a value based on internally used variation/feature
> combinations. I suggest reading up on [1]. The definitions used by either
> supplier, customer, etc. may lead to conflicts.
>
>
> [1] https://en.wikipedia.org/wiki/Stock_keeping_unit
>
> Best regards,
>
> Pierre Smits
>
> *Apache Trafodion <https://trafodion.apache.org>, Vice President*
> *Apache Directory <https://directory.apache.org>, PMC Member*
> Apache Incubator <https://incubator.apache.org>, committer
> *Apache OFBiz <https://ofbiz.apache.org>, contributor (without privileges)
> since 2008*
> Apache Steve <https://steve.apache.org>, committer
>
>
> On Thu, May 2, 2019 at 1:44 PM Suraj Khurana <[hidden email]>
> wrote:
>
> > Hello team,
> >
> > As far as my understanding, externalId in OrderHeader is used to save
> > orderId reference of any other system into our system.
> >
> > If this is the only case, we should also maintain something similar on
> > following entities as well as consistency and they will be in need in
> case
> > of a full-fledged integration with OFBiz environment with any other
> system:
> >
> >    1. ReturnHeader
> >    2. ContactMech
> >    3. Party (already exist)
> >    4. Facility
> >    5. Product (can be discussed, Identification type SKU can also do the
> >    job)
> >
> > Please let me know your thoughts on this.
> >
> > --
> > Best Regards,
> > Suraj Khurana
> > Technical Consultant
> >
>

[Jacques Le Roux]

Thanks Scott,

I think that if it's well documented the EntityIdentification could be a good solution to this problem

Jacques

Le 02/05/2019 à 22:24, Scott Gray a écrit :

> I'd tend to agree with Pierre here, following the {entity}Identification
> pattern is probably a better approach long term simply because the
> externalId pattern breaks as soon as you need more than one identifier.  If
> the likelihood of multiple IDs is low, then the {entity}Attribute pattern
> might be a better approach.
>
> But with that said, when customizing a system I'll typically just throw an
> additional field on the entity and be done with it.  It doesn't take long
> to write a helper method get{Entity}ByExternalId(String) to hook it up.
> Because there's very little business logic that OFBiz can attach to these
> fields, the amount of time we can save developers by having these fields
> available in advance is very small.  That changes with the Identification
> pattern because we can provide from/thru dating, enforce unique constraints
> and regex patterns etc. which will save developers more time.
>
> Regarding Facility, it's useful to have an external identifier when you're
> integrating/syncing with a 3PL system or in general if you don't own the
> facility that you're using.  But that could be said of almost any table in
> the system when you need to keep it in sync with another.
>
> I almost wonder if a generic entity (EntityIdentification) would be a
> better approach that contains something like:
> - entityName
> - entityId
> - fromDate
> - thruDate
> - idType
> - value
> We could then provide a set of generic helper methods/services to perform
> lookups and update values e.g. GenericValue facility =
> getEntityById("Facility", "3PL", "123").  The CRUD services could use
> another table (EntityIdentificationType) to help with enforcing contraints:
> - entityName
> - idType
> - requireUnique
> - validationRegex
>
> The main downsides would be:
> - Duplication with the current <entity>Identification pattern (confusion)
> - Lack of foreign keys back to the entities being identified
> - Largely unused pattern in general currently (I think only EntityAuditLog
> is similar)
>
> Regards
> Scott
>
> On Fri, 3 May 2019 at 00:33, Pierre Smits <[hidden email]> wrote:
>
>> Current methodology of having the externalId field in the various tables is
>> limiting the capabilities of OFBiz. With this an object can have only 1
>> externalId value. However, it is quite feasible that an object can be
>> associated with various external systems with each having a different
>> externalId value. This is particularly true for the party entity.
>>
>> I wonder whether this is necessary for a facility. If a supplier has an Id
>> value for one of the internally used facilities, why would the adopter
>> care? Similar thoughts are on contact mech and shipment. But a good example
>> and explanation for each may help.
>>
>> The external Id of a product is (as far as it is related to a product
>> supplied by a 3rd party) captured in SupplierProduct entity. If the intent
>> is to capture the product Id as it is used by a customer and other parties,
>> then the same reasoning as used for parties (see above) applies. Why would
>> an OFBiz adopter care what the external Ids of downstream parties are?
>>
>> The identification type SKU can't be used for this purpose, as it is
>> intended to have a value based on internally used variation/feature
>> combinations. I suggest reading up on [1]. The definitions used by either
>> supplier, customer, etc. may lead to conflicts.
>>
>>
>> [1] https://en.wikipedia.org/wiki/Stock_keeping_unit
>>
>> Best regards,
>>
>> Pierre Smits
>>
>> *Apache Trafodion <https://trafodion.apache.org>, Vice President*
>> *Apache Directory <https://directory.apache.org>, PMC Member*
>> Apache Incubator <https://incubator.apache.org>, committer
>> *Apache OFBiz <https://ofbiz.apache.org>, contributor (without privileges)
>> since 2008*
>> Apache Steve <https://steve.apache.org>, committer
>>
>>
>> On Thu, May 2, 2019 at 1:44 PM Suraj Khurana <[hidden email]>
>> wrote:
>>
>>> Hello team,
>>>
>>> As far as my understanding, externalId in OrderHeader is used to save
>>> orderId reference of any other system into our system.
>>>
>>> If this is the only case, we should also maintain something similar on
>>> following entities as well as consistency and they will be in need in
>> case
>>> of a full-fledged integration with OFBiz environment with any other
>> system:
>>>     1. ReturnHeader
>>>     2. ContactMech
>>>     3. Party (already exist)
>>>     4. Facility
>>>     5. Product (can be discussed, Identification type SKU can also do the
>>>     job)
>>>
>>> Please let me know your thoughts on this.
>>>
>>> --
>>> Best Regards,
>>> Suraj Khurana
>>> Technical Consultant
>>>

[Rishi Solanki]

Hello Scott, Jacques,
IMO this kind of requirement requires only for few entities not all. So
creating a generic pattern like EntityAuditLog or SequenceValueItem which
is in general applicable to most of the entity as per requirement may give
signal or OFBiz user that this is something which is globally work with
OFBiz data model. May lead OFBiz user to apply it on wrong entities.
While migrating the data from legacy system or integrating data from some
third party system causes such type of requirement. In general, it requires
only one externalId and if more requires then we use other fields of the
entity if possible or simply use Attributes entity. And these patterns are
already adopted, so I think we should think on already adopted pattern once
more.
May be we can filter the entity list from the proposal or we can choose to
ad more identification entities (5 to 10 in range) or externalId. But we
should try to go with existing pattern. And yes brainstorming on each
entity to be include must be there in process of finalizing this thread.

Also agree on all downsides mentioned by Scott. My vote is for having
identification entity pattern like GoodIdentification, PartyIdentification
or externalId after discussion on minimum set of entities. We should have
some use cases which is not specific to resolve only one problem then we
can finalize the solution.

Best Regards,
--
*Rishi Solanki* | Sr Manager, Enterprise Software Development
HotWax Systems <http://www.hotwaxsystems.com/>
Linkedin: *Rishi Solanki*
<https://www.linkedin.com/in/rishi-solanki-62271b7/>
Direct: [phone]


On Fri, May 3, 2019 at 11:59 AM Jacques Le Roux <
[hidden email]> wrote:

> Thanks Scott,
>
> I think that if it's well documented the EntityIdentification could be a
> good solution to this problem
>
> Jacques
>
> Le 02/05/2019 à 22:24, Scott Gray a écrit :
> > I'd tend to agree with Pierre here, following the {entity}Identification
> > pattern is probably a better approach long term simply because the
> > externalId pattern breaks as soon as you need more than one identifier.
> If
> > the likelihood of multiple IDs is low, then the {entity}Attribute pattern
> > might be a better approach.
> >
> > But with that said, when customizing a system I'll typically just throw
> an
> > additional field on the entity and be done with it.  It doesn't take long
> > to write a helper method get{Entity}ByExternalId(String) to hook it up.
> > Because there's very little business logic that OFBiz can attach to these
> > fields, the amount of time we can save developers by having these fields
> > available in advance is very small.  That changes with the Identification
> > pattern because we can provide from/thru dating, enforce unique
> constraints
> > and regex patterns etc. which will save developers more time.
> >
> > Regarding Facility, it's useful to have an external identifier when
> you're
> > integrating/syncing with a 3PL system or in general if you don't own the
> > facility that you're using.  But that could be said of almost any table
> in
> > the system when you need to keep it in sync with another.
> >
> > I almost wonder if a generic entity (EntityIdentification) would be a
> > better approach that contains something like:
> > - entityName
> > - entityId
> > - fromDate
> > - thruDate
> > - idType
> > - value
> > We could then provide a set of generic helper methods/services to perform
> > lookups and update values e.g. GenericValue facility =
> > getEntityById("Facility", "3PL", "123").  The CRUD services could use
> > another table (EntityIdentificationType) to help with enforcing
> contraints:
> > - entityName
> > - idType
> > - requireUnique
> > - validationRegex
> >
> > The main downsides would be:
> > - Duplication with the current <entity>Identification pattern (confusion)
> > - Lack of foreign keys back to the entities being identified
> > - Largely unused pattern in general currently (I think only
> EntityAuditLog
> > is similar)
> >
> > Regards
> > Scott
> >
> > On Fri, 3 May 2019 at 00:33, Pierre Smits <[hidden email]>
> wrote:
> >
> >> Current methodology of having the externalId field in the various
> tables is
> >> limiting the capabilities of OFBiz. With this an object can have only 1
> >> externalId value. However, it is quite feasible that an object can be
> >> associated with various external systems with each having a different
> >> externalId value. This is particularly true for the party entity.
> >>
> >> I wonder whether this is necessary for a facility. If a supplier has an
> Id
> >> value for one of the internally used facilities, why would the adopter
> >> care? Similar thoughts are on contact mech and shipment. But a good
> example
> >> and explanation for each may help.
> >>
> >> The external Id of a product is (as far as it is related to a product
> >> supplied by a 3rd party) captured in SupplierProduct entity. If the
> intent
> >> is to capture the product Id as it is used by a customer and other
> parties,
> >> then the same reasoning as used for parties (see above) applies. Why
> would
> >> an OFBiz adopter care what the external Ids of downstream parties are?
> >>
> >> The identification type SKU can't be used for this purpose, as it is
> >> intended to have a value based on internally used variation/feature
> >> combinations. I suggest reading up on [1]. The definitions used by
> either
> >> supplier, customer, etc. may lead to conflicts.
> >>
> >>
> >> [1] https://en.wikipedia.org/wiki/Stock_keeping_unit
> >>
> >> Best regards,
> >>
> >> Pierre Smits
> >>
> >> *Apache Trafodion <https://trafodion.apache.org>, Vice President*
> >> *Apache Directory <https://directory.apache.org>, PMC Member*
> >> Apache Incubator <https://incubator.apache.org>, committer
> >> *Apache OFBiz <https://ofbiz.apache.org>, contributor (without
> privileges)
> >> since 2008*
> >> Apache Steve <https://steve.apache.org>, committer
> >>
> >>
> >> On Thu, May 2, 2019 at 1:44 PM Suraj Khurana <[hidden email]>
> >> wrote:
> >>
> >>> Hello team,
> >>>
> >>> As far as my understanding, externalId in OrderHeader is used to save
> >>> orderId reference of any other system into our system.
> >>>
> >>> If this is the only case, we should also maintain something similar on
> >>> following entities as well as consistency and they will be in need in
> >> case
> >>> of a full-fledged integration with OFBiz environment with any other
> >> system:
> >>>     1. ReturnHeader
> >>>     2. ContactMech
> >>>     3. Party (already exist)
> >>>     4. Facility
> >>>     5. Product (can be discussed, Identification type SKU can also do
> the
> >>>     job)
> >>>
> >>> Please let me know your thoughts on this.
> >>>
> >>> --
> >>> Best Regards,
> >>> Suraj Khurana
> >>> Technical Consultant
> >>>
>