Ed W | 6 Jun 12:24 2007

Re: For those interested in the CRM/Entity management

Joshua D. Drake wrote:
> Here is the current model:
> http://ledger-smb.svn.sourceforge.net/viewvc/ledger-smb/trunk/sql/Pg-database.sql?view=markup&pathrev=1244
>
>   

Just shooting from the hip here:

- Would be useful to be able to "expire" a contact completely so that 
they are no longer active (or do we expire them per class?)
- Duplicate company names are quite common, ditto for people names
- Normalisation is often good, but it's also painful sometimes.  I need 
to cut and paste addresses in and out of the system and right now it 
needs loads of work to paste into each field.  Not everyone will need 
addresses and names so normalised (I have no better suggestion as to 
what to do though..)
- Multiple email addresses and tel numbers are possible.  However, also 
useful to mark one or more as "primary"
- Custom field information, especially to allow integration with other 
systems.  Eg my customers get an account code from us on another system, 
we also track the SIM codes for various purchases and it's useful to 
have that info easy to find.  Could add a generic "foreign key" field 
which can be updated with a key from another system to assist in tying 
two systems together?
- "Company" is often useful to abstract (but makes the model very 
complicated).  eg for suppliers I may have contact with multiple people 
from that business. 
- Arab names can follow a different convention to western names.
- Might want to loose middlenames as a field?  In fact for most purposes 
even breaking out forename and family name is overkill?  In practice you 
(Continue reading)

Charley Tiggs | 6 Jun 14:31 2007
Picon

Re: For those interested in the CRM/Entity management

Ed W wrote:
> Joshua D. Drake wrote:
>> Here is the current model:
>> http://ledger-smb.svn.sourceforge.net/viewvc/ledger-smb/trunk/sql/Pg-database.sql?view=markup&pathrev=1244
>>
>>   
> 
> Just shooting from the hip here:
> 
> - Would be useful to be able to "expire" a contact completely so that 
> they are no longer active (or do we expire them per class?)

Seconded.

> - Duplicate company names are quite common, ditto for people names

Very common among my clients.

> - Normalisation is often good, but it's also painful sometimes.  I need 
> to cut and paste addresses in and out of the system and right now it 
> needs loads of work to paste into each field.  Not everyone will need 
> addresses and names so normalised (I have no better suggestion as to 
> what to do though..)
> - Multiple email addresses and tel numbers are possible.  However, also 
> useful to mark one or more as "primary"

Seconded.

> - Custom field information, especially to allow integration with other 
> systems.  Eg my customers get an account code from us on another system, 
(Continue reading)

Mads Kiilerich | 6 Jun 22:45 2007

Re: For those interested in the CRM/Entity management


>> Joshua D. Drake wrote:
>>     
>>> Here is the current model:
>>> http://ledger-smb.svn.sourceforge.net/viewvc/ledger-smb/trunk/sql/Pg-database.sql?view=markup&pathrev=1244
>>>       

Many of the data insertions in the model makes sense. But salutation not 
only needs localization, but the set of salutions depends on the 
culture. So that might not belong that deep in the system.

/Mads

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
Joshua D. Drake | 7 Jun 06:19 2007

Re: For those interested in the CRM/Entity management

Ed W wrote:
> Joshua D. Drake wrote:
>> Here is the current model:
>> http://ledger-smb.svn.sourceforge.net/viewvc/ledger-smb/trunk/sql/Pg-database.sql?view=markup&pathrev=1244
>>
>>   
> 
> Just shooting from the hip here:
> 
> - Would be useful to be able to "expire" a contact completely so that 
> they are no longer active (or do we expire them per class?)

You would expire them per class. It may be, dead lead, inactive, 
expired, whatever you actually choose. Although, it may be an 
interesting idea in the future to actually track class changes and when 
they happen.

> - Duplicate company names are quite common, ditto for people names

Yep you are right, mistake on my part. Fixed.

> - Normalisation is often good, but it's also painful sometimes.  I need 
> to cut and paste addresses in and out of the system and right now it 
> needs loads of work to paste into each field.  Not everyone will need 
> addresses and names so normalised (I have no better suggestion as to 
> what to do though..)

What are you cutting and pasting out of and why aren't you using a form 
or other tool such as OOBase or Access to do it?

(Continue reading)

Charley Tiggs | 8 Jun 08:03 2007
Picon

custom fields

I'm in the position of having no choice but to use a custom field.  So, 
I have a question:

If I create a custom field and add "custom_field_name=value" to the cli 
command to insert a purchase order, will LSMB know what to do with it?

Charley

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
Stroller | 10 Jun 20:07 2007
Picon

Error: searching for customer by address field

Under AR > Customers > Reports > Search, I get the following error if  
I try to search by street name.

I assume this is a known issue & not unique to my installation, but I  
send it just in case it's otherwise.

Stroller.

DBD::Pg::st execute failed: ERROR: column "street" does not exist
Error!

SELECT ct.*, b.description AS business,
e.name AS employee, g.pricegroup,
l.description AS language, m.name AS manager
FROM customer ct
LEFT JOIN business b ON (ct.business_id = b.id)
LEFT JOIN employee e ON (ct.employee_id = e.id)
LEFT JOIN employee m ON (m.id = e.managerid)
LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
LEFT JOIN language l ON (l.code = ct.language_code)
WHERE 1 = 1 AND (lower(ct.address1) LIKE '%street%' OR lower 
(ct.address2) LIKE ''%street%'') ORDER BY name ASC
ERROR: column "street" does not exist

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
(Continue reading)

David Bandel | 11 Jun 00:22 2007
Picon

Re: Error: searching for customer by address field

On 6/10/07, Stroller <stroller@...> wrote:
> Under AR > Customers > Reports > Search, I get the following error if
> I try to search by street name.
>
> I assume this is a known issue & not unique to my installation, but I
> send it just in case it's otherwise.
>
> Stroller.
>
>
>
> DBD::Pg::st execute failed: ERROR: column "street" does not exist
> Error!
>
>
> SELECT ct.*, b.description AS business,
> e.name AS employee, g.pricegroup,
> l.description AS language, m.name AS manager
> FROM customer ct
> LEFT JOIN business b ON (ct.business_id = b.id)
> LEFT JOIN employee e ON (ct.employee_id = e.id)
> LEFT JOIN employee m ON (m.id = e.managerid)
> LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
> LEFT JOIN language l ON (l.code = ct.language_code)
> WHERE 1 = 1 AND (lower(ct.address1) LIKE '%street%' OR lower
> (ct.address2) LIKE ''%street%'') ORDER BY name ASC
> ERROR: column "street" does not exist

OK, two things:
1.  why don't you use ILIKE instead of LIKE and you can forget about
(Continue reading)

Chris Travers | 11 Jun 06:36 2007
Picon

Re: Error: searching for customer by address field

> OK, two things:
> 1.  why don't you use ILIKE instead of LIKE and you can forget about
> case (and that OR clause that's giving you the error).
> 2.  your problem is in the second "%street%".  Change the double
> quotes to single quotes and the error will go away.

Suggestions noted.  Will be committed shortly.
>
> Ciao,
>
> David A. Bandel
> --
> Focus on the dream, not the competition.
>             - Nemesis Air Racing Team motto
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> Ledger-smb-devel mailing list
> Ledger-smb-devel@...
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
(Continue reading)

Chris Travers | 11 Jun 06:41 2007
Picon

Re: Error: searching for customer by address field

On 6/10/07, Chris Travers <chris.travers@...> wrote:
> > OK, two things:
> > 1.  why don't you use ILIKE instead of LIKE and you can forget about
> > case (and that OR clause that's giving you the error).
> > 2.  your problem is in the second "%street%".  Change the double
> > quotes to single quotes and the error will go away.
>
> Suggestions noted.  Will be committed shortly.

Ok.  Committed.  People who want the fix can copy CT.pm out of SVN.

Best Wishes,
Chris Travers

> >
> > Ciao,
> >
> > David A. Bandel
> > --
> > Focus on the dream, not the competition.
> >             - Nemesis Air Racing Team motto
> >
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by DB2 Express
> > Download DB2 Express C - the FREE version of DB2 express and take
> > control of your XML. No limits. Just data. Click to get it now.
> > http://sourceforge.net/powerbar/db2/
> > _______________________________________________
> > Ledger-smb-devel mailing list
> > Ledger-smb-devel@...
(Continue reading)

Donna Robinson | 14 Jun 00:23 2007

DBD::Pg::st execute failed: ERROR: column "customer_id" does not exist


Hi,

LedgerSMB version 1.2.5

Clicking on any of the foll (+) results in an error message:

Purchases
  + Add Transaction
  + Creditor Invoice
    Reports
    + Transactions
    + Outstanding

Error message has the form:
-------------------------------------------------------------
DBD::Pg::st execute failed: ERROR: column "customer_id" does not exist
Error!

SELECT ct.name AS vendor, ct.curr AS currency, ct.id AS vendor_id,
current_date + ct.terms AS duedate, ct.notes, ct.curr AS currency
FROM vendor ct
WHERE ct.id = (select customer_id from ap where 1 = 1 AND vendor_id IS NOT 
NULL order by id DESC limit 1)
ERROR: column "customer_id" does not exist
-------------------------------------------------------------

Output from postgres:
-------------------------------------------------------------
ef-2005-6=> \d ap
(Continue reading)


Gmane