SQL-Ledger Homepage

SQL-Ledger User Forum

Forum index page

Log in | Register

Back to index page
Thread view  Board view
Gregory West(R)

E-mail

Manitoba Canada,
09.03.2016, 09:58
 

How is the shipto table related to the customer table (General)

I have to write a quick query based on the ship to address, to find out who the ultimate customer was. My problem is I am having trouble figuring out how the shipto table relates to the customer table. ie: If I have a child shipto record, how do I know who the parent record is?

Anyone have an idea how this is set up?

Gregory A. West

---
Gregory A. West
DB&W Systems Limited

JayArr(R)

Mission BC Canada,
17.05.2016, 15:47

@ Gregory West
 

How is the shipto table related to the customer table

Hi Greg

I don't know if you figured this out but here's what I came up with.

The Shipto table has a trans_id number that corresponds to the trans_id number in the ar table,

In the ar table there is a customer_id number that corresponds to id in the customer table and to trans_id in the address table giving you the customer and the mailing address.

Jeff





I have to write a quick query based on the ship to address, to find out who
the ultimate customer was. My problem is I am having trouble figuring out
how the shipto table relates to the customer table. ie: If I have a
child shipto record, how do I know who the parent record is?

Anyone have an idea how this is set up?

Gregory A. West

Gregory West(R)

E-mail

Manitoba Canada,
17.05.2016, 16:28

@ JayArr
 

How is the shipto table related to the customer table

Good afternoon Jeff.

I am looking at the three tables now. I see the trans_id on the shipto table. I can not see the trans_id on the ar table. I have id, customer_id, employee_id, department_id, bank_id and payment_id.

The id on the customer record does not always link to the trans_id in the shipto table. There must be some sort of link I am not seeing, as the shipto addresses show up when I create a sales order, but unfortunately way too many shipto records were created most are exactly the same except some small typo fixed. The question is now how to get rid of all the duplicate records, and adjust pointers to point at the correct record (the one that is left after 3 or 4 are deleted).

Does this make any sense?

Hi Greg

I don't know if you figured this out but here's what I came up with.

The Shipto table has a trans_id number that corresponds to the trans_id
number in the ar table,

In the ar table there is a customer_id number that corresponds to id in the
customer table and to trans_id in the address table giving you the customer
and the mailing address.

Jeff





I have to write a quick query based on the ship to address, to find out
who
the ultimate customer was. My problem is I am having trouble figuring
out
how the shipto table relates to the customer table. ie: If I have a
child shipto record, how do I know who the parent record is?

Anyone have an idea how this is set up?

Gregory A. West

---
Gregory A. West
DB&W Systems Limited

JayArr(R)

Mission BC Canada,
17.05.2016, 16:58

@ Gregory West
 

How is the shipto table related to the customer table

My mistake Greg

The Shipto table has a trans_id number that corresponds to the 'id' number in the ar table.

Yes, my Shipto table is filled with near identical records as well but each record is for a different invoice, don't delete them. This table isn't just a list of addresses, it'll be referred to any time you want to reprint an invoice.

Say you sell to Acme on Monday and ship to Baltimore - add 1 record to shipto table

On Wed you sell again to Acme but you ship to Clevelend - add another record to shipto table.

On Friday you sell to Acme and ship to Dallas - add another record to shipto table.

If the records will all look the same in the shipto table except for the trans_id that relates them to their specific invoice.

If you delete the "duplicates" then when you reprint the invoice from Monday you'll be in trouble, it won't be able to fetch the data on where it was shipped to and may incorrectly say it was sent to head office.

Jeff


Good afternoon Jeff.

I am looking at the three tables now. I see the trans_id on the shipto
table. I can not see the trans_id on the ar table. I have id,
customer_id, employee_id, department_id, bank_id and payment_id.

The id on the customer record does not always link to the trans_id in the
shipto table. There must be some sort of link I am not seeing, as the
shipto addresses show up when I create a sales order, but unfortunately way
too many shipto records were created most are exactly the same except some
small typo fixed. The question is now how to get rid of all the duplicate
records, and adjust pointers to point at the correct record (the one that
is left after 3 or 4 are deleted).

Does this make any sense?

Hi Greg

I don't know if you figured this out but here's what I came up with.

The Shipto table has a trans_id number that corresponds to the trans_id
number in the ar table,

In the ar table there is a customer_id number that corresponds to id in
the
customer table and to trans_id in the address table giving you the
customer
and the mailing address.

Jeff





I have to write a quick query based on the ship to address, to find
out
who
the ultimate customer was. My problem is I am having trouble
figuring
out
how the shipto table relates to the customer table. ie: If I have a
child shipto record, how do I know who the parent record is?

Anyone have an idea how this is set up?

Gregory A. West

JayArr(R)

Mission BC Canada,
17.05.2016, 18:12

@ Gregory West
 

How is the shipto table related to the customer table

Hi Greg

I thought about this some more and realized that you probably have a bunch of addresses showing up that are essentially the same when you click on the shipto button.

The way to get rid of the duplicates is NOT to delete anything from the shipto table it's to make all of the records in the shipto table the same.

Example

123 cherry Lane
123 Chery Lane
123 Cherry lan

will cause three address to show when you click 'shipto'

Open the Shipto table and make them all the same

123 Cherry Lane

and you'll only get one address in the future because when SQL is searching for shipto addresses it'll discard duplicates for that customer.

If you don't want to edit the table directly open each invoice from within SQL-Ledger, edit the shipto address to correct the typo and then re-post it.

Jeff




Don

Good afternoon Jeff.

I am looking at the three tables now. I see the trans_id on the shipto
table. I can not see the trans_id on the ar table. I have id,
customer_id, employee_id, department_id, bank_id and payment_id.

The id on the customer record does not always link to the trans_id in the
shipto table. There must be some sort of link I am not seeing, as the
shipto addresses show up when I create a sales order, but unfortunately way
too many shipto records were created most are exactly the same except some
small typo fixed. The question is now how to get rid of all the duplicate
records, and adjust pointers to point at the correct record (the one that
is left after 3 or 4 are deleted).

Does this make any sense?

Hi Greg

I don't know if you figured this out but here's what I came up with.

The Shipto table has a trans_id number that corresponds to the trans_id
number in the ar table,

In the ar table there is a customer_id number that corresponds to id in
the
customer table and to trans_id in the address table giving you the
customer
and the mailing address.

Jeff





I have to write a quick query based on the ship to address, to find
out
who
the ultimate customer was. My problem is I am having trouble
figuring
out
how the shipto table relates to the customer table. ie: If I have a
child shipto record, how do I know who the parent record is?

Anyone have an idea how this is set up?

Gregory A. West

Gregory West(R)

E-mail

Manitoba Canada,
17.05.2016, 18:26

@ JayArr
 

How is the shipto table related to the customer table

AH I think I see what is happening now. There is one 'shipto' per order. Duplicates are ignored so each shipto record is it is the same is not shown up twice.

I think I can do a quick down and dirty replace on all the extra records for all the shipto's that are bogus.

Thanks Jay, you are a saviour!

Hi Greg

I thought about this some more and realized that you probably have a bunch
of addresses showing up that are essentially the same when you click on the
shipto button.

The way to get rid of the duplicates is NOT to delete anything from the
shipto table it's to make all of the records in the shipto table the same.

Example

123 cherry Lane
123 Chery Lane
123 Cherry lan

will cause three address to show when you click 'shipto'

Open the Shipto table and make them all the same

123 Cherry Lane

and you'll only get one address in the future because when SQL is searching
for shipto addresses it'll discard duplicates for that customer.

If you don't want to edit the table directly open each invoice from within
SQL-Ledger, edit the shipto address to correct the typo and then re-post
it.

Jeff




Don

Good afternoon Jeff.

I am looking at the three tables now. I see the trans_id on the shipto
table. I can not see the trans_id on the ar table. I have id,
customer_id, employee_id, department_id, bank_id and payment_id.

The id on the customer record does not always link to the trans_id in
the
shipto table. There must be some sort of link I am not seeing, as the
shipto addresses show up when I create a sales order, but unfortunately
way
too many shipto records were created most are exactly the same except
some
small typo fixed. The question is now how to get rid of all the
duplicate
records, and adjust pointers to point at the correct record (the one
that
is left after 3 or 4 are deleted).

Does this make any sense?

Hi Greg

I don't know if you figured this out but here's what I came up with.

The Shipto table has a trans_id number that corresponds to the
trans_id
number in the ar table,

In the ar table there is a customer_id number that corresponds to id
in
the
customer table and to trans_id in the address table giving you the
customer
and the mailing address.

Jeff





I have to write a quick query based on the ship to address, to find
out
who
the ultimate customer was. My problem is I am having trouble
figuring
out
how the shipto table relates to the customer table. ie: If I have
a
child shipto record, how do I know who the parent record is?

Anyone have an idea how this is set up?

Gregory A. West

---
Gregory A. West
DB&W Systems Limited

JayArr(R)

Mission BC Canada,
17.05.2016, 19:08

@ Gregory West
 

How is the shipto table related to the customer table

Hi Greg

If it's only a few dozen just sort the table by zip code and they'll be easy to spot, I just cut/paste using PgAdmin to clean up.

Jeff

AH I think I see what is happening now. There is one 'shipto' per order.
Duplicates are ignored so each shipto record is it is the same is not shown
up twice.

I think I can do a quick down and dirty replace on all the extra records
for all the shipto's that are bogus.

Thanks Jay, you are a saviour!

Hi Greg

I thought about this some more and realized that you probably have a
bunch
of addresses showing up that are essentially the same when you click on
the
shipto button.

The way to get rid of the duplicates is NOT to delete anything from the
shipto table it's to make all of the records in the shipto table the
same.

Example

123 cherry Lane
123 Chery Lane
123 Cherry lan

will cause three address to show when you click 'shipto'

Open the Shipto table and make them all the same

123 Cherry Lane

and you'll only get one address in the future because when SQL is
searching
for shipto addresses it'll discard duplicates for that customer.

If you don't want to edit the table directly open each invoice from
within
SQL-Ledger, edit the shipto address to correct the typo and then re-post
it.

Jeff




Don

Good afternoon Jeff.

I am looking at the three tables now. I see the trans_id on the
shipto
table. I can not see the trans_id on the ar table. I have id,
customer_id, employee_id, department_id, bank_id and payment_id.

The id on the customer record does not always link to the trans_id in
the
shipto table. There must be some sort of link I am not seeing, as the
shipto addresses show up when I create a sales order, but
unfortunately
way
too many shipto records were created most are exactly the same except
some
small typo fixed. The question is now how to get rid of all the
duplicate
records, and adjust pointers to point at the correct record (the one
that
is left after 3 or 4 are deleted).

Does this make any sense?

Hi Greg

I don't know if you figured this out but here's what I came up with.

The Shipto table has a trans_id number that corresponds to the
trans_id
number in the ar table,

In the ar table there is a customer_id number that corresponds to id
in
the
customer table and to trans_id in the address table giving you the
customer
and the mailing address.

Jeff





I have to write a quick query based on the ship to address, to
find
out
who
the ultimate customer was. My problem is I am having trouble
figuring
out
how the shipto table relates to the customer table. ie: If I
have
a
child shipto record, how do I know who the parent record is?

Anyone have an idea how this is set up?

Gregory A. West

Back to index page
Thread view  Board view
959 Postings in 314 Threads, 320 registered users, 103 users online (0 registered, 103 guests)
SQL-Ledger User Forum | Admin contact
RSS-Feed
powered by my little forum