OFBiz › OFBiz - User

STATUS for entity

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

22 messages Options

Vedam B

STATUS for entity

Hi,

I am thinking to use STATUS_ITEM for PRODUCT and ecommerce for any approval.

Any suggestions.

Regards
Vedam

Scott Gray

Re: STATUS for entity

You really need to provide more details if you want to have any chance of a
receiving response.

Regards
Scott

On 20/11/2007, Vedam B <[hidden email]> wrote:

>
> Hi,
>
> I am thinking to use STATUS_ITEM for PRODUCT and ecommerce for any
> approval.
>
> Any suggestions.
>
> Regards
> Vedam
>

BJ Freeman

Re: STATUS for entity

hmmmm did not see the orginal so will use scotts reply.

I would be best if you has a ofbiz running locally with seed and demo
data then use the webtools to look at the data and show the
relationships for the entities.

The reason is that STATUSITEM (NON DB) is already used.
the other is to go thru the Data model book this came from for diagrams
of how it all hooks together in general.

You can do the same, but not as good if you use the webtools with a demo
data and look at the relationships between entities.
https://localhost:8443/webtools/control/ViewRelations?entityName=Product

Scott Gray sent the following on 11/19/2007 9:07 PM:

> You really need to provide more details if you want to have any chance of a
> receiving response.
>
> Regards
> Scott
>
> On 20/11/2007, Vedam B <[hidden email]> wrote:
>> Hi,
>>
>> I am thinking to use STATUS_ITEM for PRODUCT and ecommerce for any
>> approval.
>>
>> Any suggestions.
>>
>> Regards
>> Vedam
>>
>

SkipDever

Knowing if a service is running

I am writing some services that will run nightly and take several hours to
complete. I would like to know that the service is not already running. I
am creating a record in a special entity to record the start and stop time
and the current progress, but if for instance, the service gets shut down,
the stop time will have not been written.

Is there some way to know if a service is running?

Skip

Scott Gray

Re: Knowing if a service is running

Check out the MRP run in manufacturing.

Regards
Scott

On 20/11/2007, skip@thedevers <[hidden email]> wrote:

>
> I am writing some services that will run nightly and take several hours to
> complete. I would like to know that the service is not already
> running. I
> am creating a record in a special entity to record the start and stop time
> and the current progress, but if for instance, the service gets shut down,
> the stop time will have not been written.
>
> Is there some way to know if a service is running?
>
> Skip
>
>

SkipDever

RE: Knowing if a service is running

Thank you sire

You are chock full'o'info

Skip

-----Original Message-----
From: Scott Gray [mailto:[hidden email]]
Sent: Monday, November 19, 2007 9:52 PM
To: [hidden email]
Subject: Re: Knowing if a service is running

Check out the MRP run in manufacturing.

Regards
Scott

On 20/11/2007, skip@thedevers <[hidden email]> wrote:

SkipDever

complex-alias operator

In reply to this post by Scott Gray

Can anyone point me to some documentation on what operators are implemented
for complex-alias operator="?".

I've done a search and can find only "-".

Skip

Jacopo Cappellato

Re: Knowing if a service is running

In reply to this post by Scott Gray

That is correct; you can also check the Webtools --> Jobs screens... by
the way the jobs are stored in the JobSandbox entity.

Jacopo

Scott Gray wrote:

> Check out the MRP run in manufacturing.
>
> Regards
> Scott
>
> On 20/11/2007, skip@thedevers <[hidden email]> wrote:
>> I am writing some services that will run nightly and take several hours to
>> complete. I would like to know that the service is not already
>> running. I
>> am creating a record in a special entity to record the start and stop time
>> and the current progress, but if for instance, the service gets shut down,
>> the stop time will have not been written.
>>
>> Is there some way to know if a service is running?
>>
>> Skip
>>
>>
>

SkipDever

RE: Knowing if a service is running

In reply to this post by Scott Gray

Hmmm,

Well, this leads me to MrpServices.java and there is nothing in there about
determining if the service is already running unless possibly attempting to
delegator.removeAll() will throw an exception in some particular case.

I gotta believe the service engine has some call you can make
dispatcher.isRunning("Service"). Looking at the javadocs I see a
"processList" in JobManager with no documentation that looks hopeful. I see
"registerCallback", again with no documentation which would work if I could
guarantee that the machine did not crash.

I guess I'll dig deep into the service manager source and see what I can
come up with.

Skip

-----Original Message-----
From: Scott Gray [mailto:[hidden email]]
Sent: Monday, November 19, 2007 9:52 PM
To: [hidden email]
Subject: Re: Knowing if a service is running

Check out the MRP run in manufacturing.

Regards
Scott

On 20/11/2007, skip@thedevers <[hidden email]> wrote:

Scott Gray

Re: Knowing if a service is running

I meant look at the UI then trace back from there, when you run an mrp it
shows the job status on the next screen, also Jacopo's email contained much
more useful information.

Regards
Scott

On 20/11/2007, skip@thedevers <[hidden email]> wrote:

>
> Hmmm,
>
> Well, this leads me to MrpServices.java and there is nothing in there
> about
> determining if the service is already running unless possibly attempting
> to
> delegator.removeAll() will throw an exception in some particular case.
>
> I gotta believe the service engine has some call you can make
> dispatcher.isRunning("Service"). Looking at the javadocs I see a
> "processList" in JobManager with no documentation that looks hopeful. I
> see
> "registerCallback", again with no documentation which would work if I
> could
> guarantee that the machine did not crash.
>
> I guess I'll dig deep into the service manager source and see what I can
> come up with.
>
> Skip
>
> -----Original Message-----
> From: Scott Gray [mailto:[hidden email]]
> Sent: Monday, November 19, 2007 9:52 PM
> To: [hidden email]
> Subject: Re: Knowing if a service is running
>
>
> Check out the MRP run in manufacturing.
>
> Regards
> Scott
>
> On 20/11/2007, skip@thedevers <[hidden email]> wrote:
> >
> > I am writing some services that will run nightly and take several hours
> to
> > complete. I would like to know that the service is not already
> > running. I
> > am creating a record in a special entity to record the start and stop
> time
> > and the current progress, but if for instance, the service gets shut
> down,
> > the stop time will have not been written.
> >
> > Is there some way to know if a service is running?
> >
> > Skip
> >
> >
>
>

Vedam B

Re: STATUS for entity

In reply to this post by BJ Freeman

Hi,

At the moment there is no relationship between PRODUCT & STATUS_ITEM.

As soon as the PRODUCT created i want to store the STATUS of the PRODUCT and
should change depends on various conditions through out the PRODUCT life
cycle.

I need help on the following

1. STATUS_ITEM is meant for such requirement by design
2. Any other alternative options

Regards
Vedam

On Nov 20, 2007 11:06 AM, BJ Freeman <[hidden email]> wrote:

> hmmmm did not see the orginal so will use scotts reply.
>
> I would be best if you has a ofbiz running locally with seed and demo
> data then use the webtools to look at the data and show the
> relationships for the entities.
>
> The reason is that STATUSITEM (NON DB) is already used.
> the other is to go thru the Data model book this came from for diagrams
> of how it all hooks together in general.
>
> You can do the same, but not as good if you use the webtools with a demo
> data and look at the relationships between entities.
> https://localhost:8443/webtools/control/ViewRelations?entityName=Product
>
> Scott Gray sent the following on 11/19/2007 9:07 PM:
> > You really need to provide more details if you want to have any chance
> of a
> > receiving response.
> >
> > Regards
> > Scott
> >
> > On 20/11/2007, Vedam B <[hidden email]> wrote:
> >> Hi,
> >>
> >> I am thinking to use STATUS_ITEM for PRODUCT and ecommerce for any
> >> approval.
> >>
> >> Any suggestions.
> >>
> >> Regards
> >> Vedam
> >>
> >
>

BJ Freeman

Re: STATUS for entity

first the enddate terminates the life cycle of a product.
but the statuses are in the supplierProduct, Cart items and invenentory
Items.
What type of statuses do you see for a product not covered by these

Vedam B sent the following on 11/20/2007 5:04 PM:

> Hi,
>
> At the moment there is no relationship between PRODUCT & STATUS_ITEM.
>
> As soon as the PRODUCT created i want to store the STATUS of the PRODUCT and
> should change depends on various conditions through out the PRODUCT life
> cycle.
>
> I need help on the following
>
> 1. STATUS_ITEM is meant for such requirement by design
> 2. Any other alternative options
>
>
> Regards
> Vedam
>
> On Nov 20, 2007 11:06 AM, BJ Freeman <[hidden email]> wrote:
>
>> hmmmm did not see the orginal so will use scotts reply.
>>
>> I would be best if you has a ofbiz running locally with seed and demo
>> data then use the webtools to look at the data and show the
>> relationships for the entities.
>>
>> The reason is that STATUSITEM (NON DB) is already used.
>> the other is to go thru the Data model book this came from for diagrams
>> of how it all hooks together in general.
>>
>> You can do the same, but not as good if you use the webtools with a demo
>> data and look at the relationships between entities.
>> https://localhost:8443/webtools/control/ViewRelations?entityName=Product
>>
>> Scott Gray sent the following on 11/19/2007 9:07 PM:
>>> You really need to provide more details if you want to have any chance
>> of a
>>> receiving response.
>>>
>>> Regards
>>> Scott
>>>
>>> On 20/11/2007, Vedam B <[hidden email]> wrote:
>>>> Hi,
>>>>
>>>> I am thinking to use STATUS_ITEM for PRODUCT and ecommerce for any
>>>> approval.
>>>>
>>>> Any suggestions.
>>>>
>>>> Regards
>>>> Vedam
>>>>
>

SkipDever

Entity View Problem

In reply to this post by Jacopo Cappellato

Anyone up late who can spot the problem with this:

<view-entity entity-name="InvoiceAndItemTotal"
package-name="com.opensourcestrategies.financials.invoice"
title="Invoice and InvoiceItem Total">
<member-entity entity-alias="I" entity-name="Invoice"/>
<member-entity entity-alias="II" entity-name="InvoiceItem"/>
<alias entity-alias="I" name="partyId" />
<alias entity-alias="I" name="partyIdFrom" />
<alias entity-alias="I" name="statusId" />
<alias entity-alias="I" name="billingAccountId" />
<alias entity-alias="I" name="invoiceDate" />
<alias entity-alias="I" name="paidDate" />
<alias entity-alias="I" name="invoiceTypeId" />
<alias entity-alias="II" name="invoiceId" group-by="true" />
<alias entity-alias="II" name="orderTotal" function="sum">
<complex-alias operator="*">
<complex-alias-field entity-alias="II" field="quantity"
default-value="1"/>
<complex-alias-field entity-alias="II" field="amount"
default-value="0"/>
</complex-alias>
</alias>
<view-link entity-alias="II" rel-entity-alias="I">
<key-map field-name="invoiceId"/>
</view-link>
<relation type="one-nofk" rel-entity-name="InvoiceItem">
<key-map field-name="invoiceId"/>
<key-map field-name="invoiceItemSeqId"/>
</relation>
</view-entity>

Results in:
Target exception: org.ofbiz.entity.GenericDataSourceException: SQL Exception
while executing the following:
SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID, I.BILLING_ACCOUNT_ID,
I.INVOICE_DATE, I.PAID_DATE, I.INVOICE_TYPE_ID, I.INVOICE_ID,
SUM((COALESCE(II.QUANTITY,1) * COALESCE(II.AMOUNT,0))) FROM
OFBIZ.INVOICE_ITEM II INNER JOIN OFBIZ.INVOICE I ON II.INVOICE_ID =
I.INVOICE_ID GROUP BY I.INVOICE_ID

Thanks in advance.

Skip

Scott Gray

Re: Entity View Problem

I'm no sql expert but isn't there some rule about the select fields needing
to be in the group by?

Regards
Scott

On 21/11/2007, skip@thedevers <[hidden email]> wrote:

>
> Anyone up late who can spot the problem with this:
>
> <view-entity entity-name="InvoiceAndItemTotal"
> package-name="com.opensourcestrategies.financials.invoice"
> title="Invoice and InvoiceItem Total">
> <member-entity entity-alias="I" entity-name="Invoice"/>
> <member-entity entity-alias="II" entity-name="InvoiceItem"/>
> <alias entity-alias="I" name="partyId" />
> <alias entity-alias="I" name="partyIdFrom" />
> <alias entity-alias="I" name="statusId" />
> <alias entity-alias="I" name="billingAccountId" />
> <alias entity-alias="I" name="invoiceDate" />
> <alias entity-alias="I" name="paidDate" />
> <alias entity-alias="I" name="invoiceTypeId" />
> <alias entity-alias="II" name="invoiceId" group-by="true" />
> <alias entity-alias="II" name="orderTotal" function="sum">
> <complex-alias operator="*">
> <complex-alias-field entity-alias="II" field="quantity"
> default-value="1"/>
> <complex-alias-field entity-alias="II" field="amount"
> default-value="0"/>
> </complex-alias>
> </alias>
> <view-link entity-alias="II" rel-entity-alias="I">
> <key-map field-name="invoiceId"/>
> </view-link>
> <relation type="one-nofk" rel-entity-name="InvoiceItem">
> <key-map field-name="invoiceId"/>
> <key-map field-name="invoiceItemSeqId"/>
> </relation>
> </view-entity>
>
> Results in:
> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
> Exception
> while executing the following:
> SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID, I.BILLING_ACCOUNT_ID,
> I.INVOICE_DATE, I.PAID_DATE, I.INVOICE_TYPE_ID, I.INVOICE_ID,
> SUM((COALESCE(II.QUANTITY,1) * COALESCE(II.AMOUNT,0))) FROM
> OFBIZ.INVOICE_ITEM II INNER JOIN OFBIZ.INVOICE I ON II.INVOICE_ID =
> I.INVOICE_ID GROUP BY I.INVOICE_ID
>
> Thanks in advance.
>
> Skip
>
>

David E Jones

Re: Entity View Problem

Yeah, all of them have to be in the group by list or have a function
on them.

If there are fields you don't want to have in the group by or have a
function, then leave them out of the view-entity def or pass in a list
of fields to select for the entity and leave the undesired fields out.

BTW, this and hundreds of other similar tips are covered in the OFBiz
framework training videos.

-David

On Nov 20, 2007, at 9:57 PM, Scott Gray wrote:

> I'm no sql expert but isn't there some rule about the select fields
> needing
> to be in the group by?
>
> Regards
> Scott
>
> On 21/11/2007, skip@thedevers <[hidden email]> wrote:
>>
>> Anyone up late who can spot the problem with this:
>>
>> <view-entity entity-name="InvoiceAndItemTotal"
>> package-name="com.opensourcestrategies.financials.invoice"
>> title="Invoice and InvoiceItem Total">
>> <member-entity entity-alias="I" entity-name="Invoice"/>
>> <member-entity entity-alias="II" entity-name="InvoiceItem"/>
>> <alias entity-alias="I" name="partyId" />
>> <alias entity-alias="I" name="partyIdFrom" />
>> <alias entity-alias="I" name="statusId" />
>> <alias entity-alias="I" name="billingAccountId" />
>> <alias entity-alias="I" name="invoiceDate" />
>> <alias entity-alias="I" name="paidDate" />
>> <alias entity-alias="I" name="invoiceTypeId" />
>> <alias entity-alias="II" name="invoiceId" group-by="true" />
>> <alias entity-alias="II" name="orderTotal" function="sum">
>> <complex-alias operator="*">
>> <complex-alias-field entity-alias="II" field="quantity"
>> default-value="1"/>
>> <complex-alias-field entity-alias="II" field="amount"
>> default-value="0"/>
>> </complex-alias>
>> </alias>
>> <view-link entity-alias="II" rel-entity-alias="I">
>> <key-map field-name="invoiceId"/>
>> </view-link>
>> <relation type="one-nofk" rel-entity-name="InvoiceItem">
>> <key-map field-name="invoiceId"/>
>> <key-map field-name="invoiceItemSeqId"/>
>> </relation>
>> </view-entity>
>>
>> Results in:
>> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
>> Exception
>> while executing the following:
>> SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID,
>> I.BILLING_ACCOUNT_ID,
>> I.INVOICE_DATE, I.PAID_DATE, I.INVOICE_TYPE_ID, I.INVOICE_ID,
>> SUM((COALESCE(II.QUANTITY,1) * COALESCE(II.AMOUNT,0))) FROM
>> OFBIZ.INVOICE_ITEM II INNER JOIN OFBIZ.INVOICE I ON II.INVOICE_ID =
>> I.INVOICE_ID GROUP BY I.INVOICE_ID
>>
>> Thanks in advance.
>>
>> Skip
>>
>>

smime.p7s (3K) Download Attachment

SkipDever

RE: Entity View Problem

In reply to this post by Scott Gray

Scott

You are exactly right. Thats the problem. I have been thinking about this
for half an hour now and can't figure out how to get around it in Ofbiz. In
Ofbiz, I either have to apply a sum, avg, min, max, etc or set
group-by="true". I expect putting in the extra group-by won't cause
problems, but it just seems messy.

What I really want is function="pick the first you come across".

I am no SQL expert either. Maybe there is one who wants to comment?

Actually, as it turns out, you can have the field in the groupby clause or
the where clause.

Skip

-----Original Message-----
From: Scott Gray [mailto:[hidden email]]
Sent: Tuesday, November 20, 2007 8:57 PM
To: [hidden email]
Subject: Re: Entity View Problem

I'm no sql expert but isn't there some rule about the select fields needing
to be in the group by?

Regards
Scott

On 21/11/2007, skip@thedevers <[hidden email]> wrote:

SkipDever

RE: Entity View Problem

In reply to this post by David E Jones

David

Thanks for pointing this out. I watched every one of those very helpful
dudes, but unfortunately, that was 4 months ago now.

Guess I better to watch them again now that I need the info.

Skip

-----Original Message-----
From: David E Jones [mailto:[hidden email]]
Sent: Tuesday, November 20, 2007 9:15 PM
To: [hidden email]
Subject: Re: Entity View Problem

Yeah, all of them have to be in the group by list or have a function
on them.

If there are fields you don't want to have in the group by or have a
function, then leave them out of the view-entity def or pass in a list
of fields to select for the entity and leave the undesired fields out.

BTW, this and hundreds of other similar tips are covered in the OFBiz
framework training videos.

-David

On Nov 20, 2007, at 9:57 PM, Scott Gray wrote:

SkipDever

RE: Entity View Problem

In reply to this post by Scott Gray

Well, as we all know now, you must have either an aggregate function or
group-by clause unless your select statement does not reference the fields
in the view. So, I did this (note the use of the "max" function on the
string and date fields) just as a test:

<view-entity entity-name="InvoiceAndItemTotal"
package-name="org.ofbiz.accounting.ar"
title="Invoice and InvoiceItem Total">
<member-entity entity-alias="I" entity-name="Invoice"/>
<member-entity entity-alias="II" entity-name="InvoiceItem"/>
<alias entity-alias="II" name="invoiceId" group-by="true" />
<alias entity-alias="I" name="partyId" group-by="true"/>
<alias entity-alias="I" name="partyIdFrom" group-by="true"/>
<alias entity-alias="I" name="statusId" function="max" />
<alias entity-alias="I" name="billingAccountId" function="max"/>
<alias entity-alias="I" name="invoiceDate" function="max"/>
<alias entity-alias="I" name="paidDate" group-by="true"/>
<alias entity-alias="I" name="invoiceTypeId" function="max"/>
<alias entity-alias="II" name="orderTotal" function="sum">
<complex-alias operator="*">
<complex-alias-field entity-alias="II" field="quantity"
default-value="1"/>
<complex-alias-field entity-alias="II" field="amount"
default-value="0"/>
</complex-alias>
</alias>
<view-link entity-alias="I" rel-entity-alias="II">
<key-map field-name="invoiceId"/>
</view-link>
</view-entity>

This actually works in both derby and postgres and the raw sql works in
mysql (didnt actually try hooking it up, just ran a similiar test). Not
entirely sure of the impact on performance as this was a small data set.

Fun as this was, what I really want to do is just sum the InvoiceItem parts
and relate the results to Invoice in a single view.

Is there a way to do this? Can I create a view from a view-entity and
entity?

Skip

-----Original Message-----
From: Scott Gray [mailto:[hidden email]]
Sent: Tuesday, November 20, 2007 8:57 PM
To: [hidden email]
Subject: Re: Entity View Problem

I'm no sql expert but isn't there some rule about the select fields needing
to be in the group by?

Regards
Scott

On 21/11/2007, skip@thedevers <[hidden email]> wrote:

Jacopo Cappellato

Re: Entity View Problem

Skip,

you should include in the select statement only the fields that you want
to display, and all these fields must have the group-by attribute set
(or an aggregate function); for the remaining fields that you don't want
to display (or that are only used in the "where" clause) you don't need
to add the aggregate/group-by attribute.

Jacopo

skip@thedevers wrote:

> Well, as we all know now, you must have either an aggregate function or
> group-by clause unless your select statement does not reference the fields
> in the view. So, I did this (note the use of the "max" function on the
> string and date fields) just as a test:
>
> <view-entity entity-name="InvoiceAndItemTotal"
> package-name="org.ofbiz.accounting.ar"
> title="Invoice and InvoiceItem Total">
> <member-entity entity-alias="I" entity-name="Invoice"/>
> <member-entity entity-alias="II" entity-name="InvoiceItem"/>
> <alias entity-alias="II" name="invoiceId" group-by="true" />
> <alias entity-alias="I" name="partyId" group-by="true"/>
> <alias entity-alias="I" name="partyIdFrom" group-by="true"/>
> <alias entity-alias="I" name="statusId" function="max" />
> <alias entity-alias="I" name="billingAccountId" function="max"/>
> <alias entity-alias="I" name="invoiceDate" function="max"/>
> <alias entity-alias="I" name="paidDate" group-by="true"/>
> <alias entity-alias="I" name="invoiceTypeId" function="max"/>
> <alias entity-alias="II" name="orderTotal" function="sum">
> <complex-alias operator="*">
> <complex-alias-field entity-alias="II" field="quantity"
> default-value="1"/>
> <complex-alias-field entity-alias="II" field="amount"
> default-value="0"/>
> </complex-alias>
> </alias>
> <view-link entity-alias="I" rel-entity-alias="II">
> <key-map field-name="invoiceId"/>
> </view-link>
> </view-entity>
>
> This actually works in both derby and postgres and the raw sql works in
> mysql (didnt actually try hooking it up, just ran a similiar test). Not
> entirely sure of the impact on performance as this was a small data set.
>
> Fun as this was, what I really want to do is just sum the InvoiceItem parts
> and relate the results to Invoice in a single view.
>
> Is there a way to do this? Can I create a view from a view-entity and
> entity?
>
> Skip
>
> -----Original Message-----
> From: Scott Gray [mailto:[hidden email]]
> Sent: Tuesday, November 20, 2007 8:57 PM
> To: [hidden email]
> Subject: Re: Entity View Problem
>
>
> I'm no sql expert but isn't there some rule about the select fields needing
> to be in the group by?
>
> Regards
> Scott
>
> On 21/11/2007, skip@thedevers <[hidden email]> wrote:
>> Anyone up late who can spot the problem with this:
>>
>> <view-entity entity-name="InvoiceAndItemTotal"
>> package-name="com.opensourcestrategies.financials.invoice"
>> title="Invoice and InvoiceItem Total">
>> <member-entity entity-alias="I" entity-name="Invoice"/>
>> <member-entity entity-alias="II" entity-name="InvoiceItem"/>
>> <alias entity-alias="I" name="partyId" />
>> <alias entity-alias="I" name="partyIdFrom" />
>> <alias entity-alias="I" name="statusId" />
>> <alias entity-alias="I" name="billingAccountId" />
>> <alias entity-alias="I" name="invoiceDate" />
>> <alias entity-alias="I" name="paidDate" />
>> <alias entity-alias="I" name="invoiceTypeId" />
>> <alias entity-alias="II" name="invoiceId" group-by="true" />
>> <alias entity-alias="II" name="orderTotal" function="sum">
>> <complex-alias operator="*">
>> <complex-alias-field entity-alias="II" field="quantity"
>> default-value="1"/>
>> <complex-alias-field entity-alias="II" field="amount"
>> default-value="0"/>
>> </complex-alias>
>> </alias>
>> <view-link entity-alias="II" rel-entity-alias="I">
>> <key-map field-name="invoiceId"/>
>> </view-link>
>> <relation type="one-nofk" rel-entity-name="InvoiceItem">
>> <key-map field-name="invoiceId"/>
>> <key-map field-name="invoiceItemSeqId"/>
>> </relation>
>> </view-entity>
>>
>> Results in:
>> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
>> Exception
>> while executing the following:
>> SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID, I.BILLING_ACCOUNT_ID,
>> I.INVOICE_DATE, I.PAID_DATE, I.INVOICE_TYPE_ID, I.INVOICE_ID,
>> SUM((COALESCE(II.QUANTITY,1) * COALESCE(II.AMOUNT,0))) FROM
>> OFBIZ.INVOICE_ITEM II INNER JOIN OFBIZ.INVOICE I ON II.INVOICE_ID =
>> I.INVOICE_ID GROUP BY I.INVOICE_ID
>>
>> Thanks in advance.
>>
>> Skip
>>
>>

Christopher L

RE: Entity View Problem

In reply to this post by SkipDever

Added to wiki per your request.

C

-----Original Message-----
From: skip@thedevers [mailto:[hidden email]]
Sent: Wednesday, November 21, 2007 12:00 AM
To: clearchris
Subject: RE: Entity View Problem

clearchris

A very useful tip and it pointed out the problem instantly.

Thanks.

This in my view should go on the tips wiki page.

Skip

-----Original Message-----
From: clearchris [mailto:[hidden email]]
Sent: Tuesday, November 20, 2007 8:32 PM
To: [hidden email]
Subject: RE: Entity View Problem

You can usually get a better error message by running the query directly on
the DB.

C

-----Original Message-----
From: skip@thedevers [mailto:[hidden email]]
Sent: Tuesday, November 20, 2007 10:05 PM
To: [hidden email]
Subject: Entity View Problem

Anyone up late who can spot the problem with this:

<view-entity entity-name="InvoiceAndItemTotal"
package-name="com.opensourcestrategies.financials.invoice"
title="Invoice and InvoiceItem Total">
<member-entity entity-alias="I" entity-name="Invoice"/>
<member-entity entity-alias="II" entity-name="InvoiceItem"/>
<alias entity-alias="I" name="partyId" />
<alias entity-alias="I" name="partyIdFrom" />
<alias entity-alias="I" name="statusId" />
<alias entity-alias="I" name="billingAccountId" />
<alias entity-alias="I" name="invoiceDate" />
<alias entity-alias="I" name="paidDate" />
<alias entity-alias="I" name="invoiceTypeId" />
<alias entity-alias="II" name="invoiceId" group-by="true" />
<alias entity-alias="II" name="orderTotal" function="sum">
<complex-alias operator="*">
<complex-alias-field entity-alias="II" field="quantity"
default-value="1"/>
<complex-alias-field entity-alias="II" field="amount"
default-value="0"/>
</complex-alias>
</alias>
<view-link entity-alias="II" rel-entity-alias="I">
<key-map field-name="invoiceId"/>
</view-link>
<relation type="one-nofk" rel-entity-name="InvoiceItem">
<key-map field-name="invoiceId"/>
<key-map field-name="invoiceItemSeqId"/>
</relation>
</view-entity>

Results in:
Target exception: org.ofbiz.entity.GenericDataSourceException: SQL Exception
while executing the following:
SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID, I.BILLING_ACCOUNT_ID,
I.INVOICE_DATE, I.PAID_DATE, I.INVOICE_TYPE_ID, I.INVOICE_ID,
SUM((COALESCE(II.QUANTITY,1) * COALESCE(II.AMOUNT,0))) FROM
OFBIZ.INVOICE_ITEM II INNER JOIN OFBIZ.INVOICE I ON II.INVOICE_ID =
I.INVOICE_ID GROUP BY I.INVOICE_ID

Thanks in advance.

Skip