You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Skip <sk...@thedevers.org> on 2013/11/15 19:57:46 UTC
View Entity Question
I have this view that gives the quantity on hand for requested products:
<view-entity entity-name="ProductInventoryItemAndFacilitySum"
package-name="org.ofbiz.product.product"
title="View entity joining Product and InventoryItem to allow
queries for InventoryItem based on product attributes">
<member-entity entity-alias="PR" entity-name="Product"/>
<member-entity entity-alias="II" entity-name="InventoryItem"/>
<alias entity-alias="II" name="facilityId" group-by="true"/>
<alias entity-alias="PR" name="productId" group-by="true"/>
<alias entity-alias="PR" name="description" group-by="true"/>
<alias entity-alias="PR" name="brandName" group-by="true"/>
<alias entity-alias="PR" name="productName" group-by="true"/>
<alias entity-alias="PR" name="internalName" group-by="true"/>
<alias entity-alias="PR" name="normallyStocked" group-by="true"/>
<alias entity-alias="PR" name="isCatalogItem" group-by="true"/>
<alias entity-alias="PR" name="productTypeId" group-by="true"/>
<alias entity-alias="II" name="availableToPromiseTotal"
function="sum"/>
<alias entity-alias="II" name="quantityOnHandTotal" function="sum"/>
<view-link entity-alias="PR" rel-entity-alias="II">
<key-map field-name="productId"/>
</view-link>
<relation type="one" fk-name="PROD_INV_SUM_PROD"
rel-entity-name="Product">
<key-map field-name="productId"/>
</relation>
<relation type="many" fk-name="PROD_INV_SUM_PRC"
rel-entity-name="ProductPrice">
<key-map field-name="productId"/>
</relation>
</view-entity>
However, if there is no InventoryItem record for the Product, there are no
results. I would like availableToPromiseTotal to return zero (or null) if
there are no InventoryItems for the Product. I can do this by making two
queries, but it would be nice to get it in one. Is there a way to say that
the InventoryItem is not required in the join?
Skip
Re: View Entity Question
Posted by Rishi Solanki <ri...@gmail.com>.
+1 Jacques.
Rishi Solanki
Manager, Enterprise Software Development
HotWax Media Pvt. Ltd.
Direct: +91-9893287847
http://www.hotwaxmedia.com
On Tue, Nov 19, 2013 at 5:13 AM, Jacques Le Roux <
jacques.le.roux@les7arts.com> wrote:
> I always thought that Deepak was of good advices :)
>
> Jacques
>
> On Tuesday, November 19, 2013 12:18 AM Skip <sk...@thedevers.org> wrote:
> > Deepak
> >
> > Thanks, that is exactly what I needed. For any of those who need a
> Product
> > list with on hand quantities, this works a charm with Deepaks
> modification.
> >
> > Skip
> >
> > -----Original Message-----
> > From: Deepak Dixit [mailto:deepak.dixit@hotwaxmedia.com]
> > Sent: Friday, November 15, 2013 9:58 PM
> > To: user@ofbiz.apache.org
> > Subject: Re: View Entity Question
> >
> >
> > Hi Skip,
> >
> > Please try with rel-optional="true";
> >
> > <view-link entity-alias="PR" rel-entity-alias="II" rel-optional="true>
> > <key-map field-name="productId"/>
> > </view-link>
> >
> > Thanks & Regards
> >
> >> I have this view that gives the quantity on hand for requested products:
> >>
> >> <view-entity entity-name="ProductInventoryItemAndFacilitySum"
> >> package-name="org.ofbiz.product.product"
> >> title="View entity joining Product and InventoryItem to allow
> >> queries for InventoryItem based on product attributes">
> >> <member-entity entity-alias="PR" entity-name="Product"/>
> >> <member-entity entity-alias="II" entity-name="InventoryItem"/>
> >> <alias entity-alias="II" name="facilityId" group-by="true"/>
> >> <alias entity-alias="PR" name="productId" group-by="true"/>
> >> <alias entity-alias="PR" name="description" group-by="true"/>
> >> <alias entity-alias="PR" name="brandName" group-by="true"/>
> >> <alias entity-alias="PR" name="productName" group-by="true"/>
> >> <alias entity-alias="PR" name="internalName" group-by="true"/>
> >> <alias entity-alias="PR" name="normallyStocked" group-by="true"/>
> >> <alias entity-alias="PR" name="isCatalogItem" group-by="true"/>
> >> <alias entity-alias="PR" name="productTypeId" group-by="true"/>
> >> <alias entity-alias="II" name="availableToPromiseTotal"
> >> function="sum"/>
> >> <alias entity-alias="II" name="quantityOnHandTotal"
> function="sum"/>
> >> <view-link entity-alias="PR" rel-entity-alias="II">
> >> <key-map field-name="productId"/>
> >> </view-link>
> >> <relation type="one" fk-name="PROD_INV_SUM_PROD"
> >> rel-entity-name="Product">
> >> <key-map field-name="productId"/>
> >> </relation>
> >> <relation type="many" fk-name="PROD_INV_SUM_PRC"
> >> rel-entity-name="ProductPrice">
> >> <key-map field-name="productId"/>
> >> </relation>
> >> </view-entity>
> >>
> >> However, if there is no InventoryItem record for the Product, there are
> no
> >> results. I would like availableToPromiseTotal to return zero (or null)
> if
> >> there are no InventoryItems for the Product. I can do this by making
> two
> >> queries, but it would be nice to get it in one. Is there a way to say
> that
> >> the InventoryItem is not required in the join?
> >>
> >> Skip
>
Re: View Entity Question
Posted by Deepak Dixit <de...@hotwaxmedia.com>.
Thanks Jacques, Happy to contribute back to community but not in proportion we get from it :)
Skip, glad to know suggestion works for you.
Thanks & Regards
--
Deepak Dixit
On Nov 19, 2013, at 5:13 AM, Jacques Le Roux wrote:
> I always thought that Deepak was of good advices :)
>
> Jacques
>
> On Tuesday, November 19, 2013 12:18 AM Skip <sk...@thedevers.org> wrote:
>> Deepak
>>
>> Thanks, that is exactly what I needed. For any of those who need a Product
>> list with on hand quantities, this works a charm with Deepaks modification.
>>
>> Skip
>>
>> -----Original Message-----
>> From: Deepak Dixit [mailto:deepak.dixit@hotwaxmedia.com]
>> Sent: Friday, November 15, 2013 9:58 PM
>> To: user@ofbiz.apache.org
>> Subject: Re: View Entity Question
>>
>>
>> Hi Skip,
>>
>> Please try with rel-optional="true";
>>
>> <view-link entity-alias="PR" rel-entity-alias="II" rel-optional="true>
>> <key-map field-name="productId"/>
>> </view-link>
>>
>> Thanks & Regards
>>
>>> I have this view that gives the quantity on hand for requested products:
>>>
>>> <view-entity entity-name="ProductInventoryItemAndFacilitySum"
>>> package-name="org.ofbiz.product.product"
>>> title="View entity joining Product and InventoryItem to allow
>>> queries for InventoryItem based on product attributes">
>>> <member-entity entity-alias="PR" entity-name="Product"/>
>>> <member-entity entity-alias="II" entity-name="InventoryItem"/>
>>> <alias entity-alias="II" name="facilityId" group-by="true"/>
>>> <alias entity-alias="PR" name="productId" group-by="true"/>
>>> <alias entity-alias="PR" name="description" group-by="true"/>
>>> <alias entity-alias="PR" name="brandName" group-by="true"/>
>>> <alias entity-alias="PR" name="productName" group-by="true"/>
>>> <alias entity-alias="PR" name="internalName" group-by="true"/>
>>> <alias entity-alias="PR" name="normallyStocked" group-by="true"/>
>>> <alias entity-alias="PR" name="isCatalogItem" group-by="true"/>
>>> <alias entity-alias="PR" name="productTypeId" group-by="true"/>
>>> <alias entity-alias="II" name="availableToPromiseTotal"
>>> function="sum"/>
>>> <alias entity-alias="II" name="quantityOnHandTotal" function="sum"/>
>>> <view-link entity-alias="PR" rel-entity-alias="II">
>>> <key-map field-name="productId"/>
>>> </view-link>
>>> <relation type="one" fk-name="PROD_INV_SUM_PROD"
>>> rel-entity-name="Product">
>>> <key-map field-name="productId"/>
>>> </relation>
>>> <relation type="many" fk-name="PROD_INV_SUM_PRC"
>>> rel-entity-name="ProductPrice">
>>> <key-map field-name="productId"/>
>>> </relation>
>>> </view-entity>
>>>
>>> However, if there is no InventoryItem record for the Product, there are no
>>> results. I would like availableToPromiseTotal to return zero (or null) if
>>> there are no InventoryItems for the Product. I can do this by making two
>>> queries, but it would be nice to get it in one. Is there a way to say that
>>> the InventoryItem is not required in the join?
>>>
>>> Skip
Re: View Entity Question
Posted by Jacques Le Roux <ja...@les7arts.com>.
I always thought that Deepak was of good advices :)
Jacques
On Tuesday, November 19, 2013 12:18 AM Skip <sk...@thedevers.org> wrote:
> Deepak
>
> Thanks, that is exactly what I needed. For any of those who need a Product
> list with on hand quantities, this works a charm with Deepaks modification.
>
> Skip
>
> -----Original Message-----
> From: Deepak Dixit [mailto:deepak.dixit@hotwaxmedia.com]
> Sent: Friday, November 15, 2013 9:58 PM
> To: user@ofbiz.apache.org
> Subject: Re: View Entity Question
>
>
> Hi Skip,
>
> Please try with rel-optional="true";
>
> <view-link entity-alias="PR" rel-entity-alias="II" rel-optional="true>
> <key-map field-name="productId"/>
> </view-link>
>
> Thanks & Regards
>
>> I have this view that gives the quantity on hand for requested products:
>>
>> <view-entity entity-name="ProductInventoryItemAndFacilitySum"
>> package-name="org.ofbiz.product.product"
>> title="View entity joining Product and InventoryItem to allow
>> queries for InventoryItem based on product attributes">
>> <member-entity entity-alias="PR" entity-name="Product"/>
>> <member-entity entity-alias="II" entity-name="InventoryItem"/>
>> <alias entity-alias="II" name="facilityId" group-by="true"/>
>> <alias entity-alias="PR" name="productId" group-by="true"/>
>> <alias entity-alias="PR" name="description" group-by="true"/>
>> <alias entity-alias="PR" name="brandName" group-by="true"/>
>> <alias entity-alias="PR" name="productName" group-by="true"/>
>> <alias entity-alias="PR" name="internalName" group-by="true"/>
>> <alias entity-alias="PR" name="normallyStocked" group-by="true"/>
>> <alias entity-alias="PR" name="isCatalogItem" group-by="true"/>
>> <alias entity-alias="PR" name="productTypeId" group-by="true"/>
>> <alias entity-alias="II" name="availableToPromiseTotal"
>> function="sum"/>
>> <alias entity-alias="II" name="quantityOnHandTotal" function="sum"/>
>> <view-link entity-alias="PR" rel-entity-alias="II">
>> <key-map field-name="productId"/>
>> </view-link>
>> <relation type="one" fk-name="PROD_INV_SUM_PROD"
>> rel-entity-name="Product">
>> <key-map field-name="productId"/>
>> </relation>
>> <relation type="many" fk-name="PROD_INV_SUM_PRC"
>> rel-entity-name="ProductPrice">
>> <key-map field-name="productId"/>
>> </relation>
>> </view-entity>
>>
>> However, if there is no InventoryItem record for the Product, there are no
>> results. I would like availableToPromiseTotal to return zero (or null) if
>> there are no InventoryItems for the Product. I can do this by making two
>> queries, but it would be nice to get it in one. Is there a way to say that
>> the InventoryItem is not required in the join?
>>
>> Skip
RE: View Entity Question
Posted by Skip <sk...@thedevers.org>.
Deepak
Thanks, that is exactly what I needed. For any of those who need a Product
list with on hand quantities, this works a charm with Deepaks modification.
Skip
-----Original Message-----
From: Deepak Dixit [mailto:deepak.dixit@hotwaxmedia.com]
Sent: Friday, November 15, 2013 9:58 PM
To: user@ofbiz.apache.org
Subject: Re: View Entity Question
Hi Skip,
Please try with rel-optional="true";
<view-link entity-alias="PR" rel-entity-alias="II" rel-optional="true>
<key-map field-name="productId"/>
</view-link>
Thanks & Regards
--
Deepak Dixit
On Nov 16, 2013, at 12:27 AM, Skip wrote:
> I have this view that gives the quantity on hand for requested products:
>
> <view-entity entity-name="ProductInventoryItemAndFacilitySum"
> package-name="org.ofbiz.product.product"
> title="View entity joining Product and InventoryItem to allow
> queries for InventoryItem based on product attributes">
> <member-entity entity-alias="PR" entity-name="Product"/>
> <member-entity entity-alias="II" entity-name="InventoryItem"/>
> <alias entity-alias="II" name="facilityId" group-by="true"/>
> <alias entity-alias="PR" name="productId" group-by="true"/>
> <alias entity-alias="PR" name="description" group-by="true"/>
> <alias entity-alias="PR" name="brandName" group-by="true"/>
> <alias entity-alias="PR" name="productName" group-by="true"/>
> <alias entity-alias="PR" name="internalName" group-by="true"/>
> <alias entity-alias="PR" name="normallyStocked" group-by="true"/>
> <alias entity-alias="PR" name="isCatalogItem" group-by="true"/>
> <alias entity-alias="PR" name="productTypeId" group-by="true"/>
> <alias entity-alias="II" name="availableToPromiseTotal"
> function="sum"/>
> <alias entity-alias="II" name="quantityOnHandTotal"
function="sum"/>
> <view-link entity-alias="PR" rel-entity-alias="II">
> <key-map field-name="productId"/>
> </view-link>
> <relation type="one" fk-name="PROD_INV_SUM_PROD"
> rel-entity-name="Product">
> <key-map field-name="productId"/>
> </relation>
> <relation type="many" fk-name="PROD_INV_SUM_PRC"
> rel-entity-name="ProductPrice">
> <key-map field-name="productId"/>
> </relation>
> </view-entity>
>
> However, if there is no InventoryItem record for the Product, there are no
> results. I would like availableToPromiseTotal to return zero (or null) if
> there are no InventoryItems for the Product. I can do this by making two
> queries, but it would be nice to get it in one. Is there a way to say
that
> the InventoryItem is not required in the join?
>
> Skip
>
Re: View Entity Question
Posted by Deepak Dixit <de...@hotwaxmedia.com>.
Hi Skip,
Please try with rel-optional="true";
<view-link entity-alias="PR" rel-entity-alias="II" rel-optional="true>
<key-map field-name="productId"/>
</view-link>
Thanks & Regards
--
Deepak Dixit
On Nov 16, 2013, at 12:27 AM, Skip wrote:
> I have this view that gives the quantity on hand for requested products:
>
> <view-entity entity-name="ProductInventoryItemAndFacilitySum"
> package-name="org.ofbiz.product.product"
> title="View entity joining Product and InventoryItem to allow
> queries for InventoryItem based on product attributes">
> <member-entity entity-alias="PR" entity-name="Product"/>
> <member-entity entity-alias="II" entity-name="InventoryItem"/>
> <alias entity-alias="II" name="facilityId" group-by="true"/>
> <alias entity-alias="PR" name="productId" group-by="true"/>
> <alias entity-alias="PR" name="description" group-by="true"/>
> <alias entity-alias="PR" name="brandName" group-by="true"/>
> <alias entity-alias="PR" name="productName" group-by="true"/>
> <alias entity-alias="PR" name="internalName" group-by="true"/>
> <alias entity-alias="PR" name="normallyStocked" group-by="true"/>
> <alias entity-alias="PR" name="isCatalogItem" group-by="true"/>
> <alias entity-alias="PR" name="productTypeId" group-by="true"/>
> <alias entity-alias="II" name="availableToPromiseTotal"
> function="sum"/>
> <alias entity-alias="II" name="quantityOnHandTotal" function="sum"/>
> <view-link entity-alias="PR" rel-entity-alias="II">
> <key-map field-name="productId"/>
> </view-link>
> <relation type="one" fk-name="PROD_INV_SUM_PROD"
> rel-entity-name="Product">
> <key-map field-name="productId"/>
> </relation>
> <relation type="many" fk-name="PROD_INV_SUM_PRC"
> rel-entity-name="ProductPrice">
> <key-map field-name="productId"/>
> </relation>
> </view-entity>
>
> However, if there is no InventoryItem record for the Product, there are no
> results. I would like availableToPromiseTotal to return zero (or null) if
> there are no InventoryItems for the Product. I can do this by making two
> queries, but it would be nice to get it in one. Is there a way to say that
> the InventoryItem is not required in the join?
>
> Skip
>