Re: Ok, maybe it isn't worth upgrading all the data...

[Chris Travers <..hidden..>]

So this is the query I have added to sql/fixes.   It is safe for multiple runs.

UPDATE parts
   SET income_accno_id = (SELECT account.id
                            FROM account JOIN lsmb12.chart USING (accno)
                           WHERE chart.id = income_accno_id),
       expense_accno_id = (SELECT account.id
                            FROM account JOIN lsmb12.chart USING (accno)
                           WHERE chart.id = expense_accno_id),
       inventory_accno_id = (SELECT account.id
                            FROM account JOIN lsmb12.chart USING (accno)
                           WHERE chart.id = inventory_accno_id)
 WHERE id IN (SELECT id FROM lsmb12.parts op
               WHERE op.id = parts.id
                     AND op.income_accno_id = parts.income_accno_id
                     AND op.inventory_accno_id = parts.inventory_accno_id
                     AND op.expense_accno_id = parts.expense_accno_id);

It will run on upgrade from 1.2 and on upgrade within 1.3.x starting with 1.3.8

Best Wishes,
Chris Travers
On Sun, Dec 4, 2011 at 6:59 PM, Chris Travers <..hidden..> wrote:
> Hi Louis;
>
> I will have queries to fix this that can be rerun out to you today.
>
> Best Wishe,s
> Chris Travers
>
> On Sun, Dec 4, 2011 at 6:03 PM,  <..hidden..> wrote:
>>
>>
>> On Sat, 3 Dec 2011, Chris Travers wrote:
>>
>>> Ok.  So a couple questions:
>>> 1)  Is there an appropriate AP account set up?
>>
>> there are two service items on the invoice, each have income_accno_id of
>> 10033 and expense_accno_id 10103. I can't find an id of 10033 or 10103 in
>> account_heading or account.
>>
>> select id,accno,description,category,heading from account where id=43;
>>  id | accno |        description        | category | heading
>> ----+-------+---------------------------+----------+---------
>>  43 | 6221  | ABE Commissions and Fees  | E        |   10098
>>
>> is where I expect the charges to go.
>>
>> select id,description,income_accno_id,expense_accno_id,project_id from
>> parts where partnumber ~* 'F';
>>
>>   id   |            description            | income_accno_id | expense_accno_id | project_id
>> -------+-----------------------------------+-----------------+------------------+------------
>>  14299 | ABE Commissions on sales          |           10033 |            10103 |
>>  14300 | ABE Credit Card handling fees     |           10033 |            10103 |
>>
>> are the parts.
>>
>>
>>
>>
>>
>>> 2)  If hte parts were migrated check to see if the income_accno_id,
>>> inventory_accno_id, and expense_accno_id values are found in
>>> account.id.
>>>
>>> Best Wishes,
>>> Chris Travers
>>>
>>> ------------------------------------------------------------------------------
>>> All the data continuously generated in your IT infrastructure
>>> contains a definitive record of customers, application performance,
>>> security threats, fraudulent activity, and more. Splunk takes this
>>> data and makes sense of it. IT sense. And common sense.
>>> http://p.sf.net/sfu/splunk-novd2d
>>> _______________________________________________
>>> Ledger-smb-users mailing list
>>> ..hidden..
>>> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>>>
>>
>> ------------------------------------------------------------------------------
>> All the data continuously generated in your IT infrastructure
>> contains a definitive record of customers, application performance,
>> security threats, fraudulent activity, and more. Splunk takes this
>> data and makes sense of it. IT sense. And common sense.
>> http://p.sf.net/sfu/splunk-novd2d
>> _______________________________________________
>> Ledger-smb-users mailing list
>> ..hidden..
>> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users