Sage 100

I realize it's not recommended, but I'm testing a theory about getting a new client's sales history into Sage.

I found the note "How to Add Fields to a VI Import File" so I've been able to add Qty and Unit Cost, etc to my import job, but now I have a message indicating "Cannot add records to IM_ItemTransactionHistory". 

Can someone guide me to the hidden area to allow that?

Thanks in advance, Robert

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON
------------------------------

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

I'm not sure what you mean by posting individual batches one at a time.  Is it only sales history and not PO receipt history?  If so, why not import to SO_invoiceHeader and SO_InvoiceDetail in a test company copy of live, update,then copy IM_ItemTransactionHistory back to the live company?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

Original Message:
Sent: 03-26-2021 16:27
From: Robert White
Subject: Importing into IM_ItemTransactionHistory

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

@Doug Higgs Seems like this would work cleanly.  Except for getting the sales into monthly buckets in period summary posting.  You could do a crystal report to sort sales by invoice date from invoice history.  ​

------------------------------
alan niergarth
Velosio LLC
------------------------------

Original Message:
Sent: 03-26-2021 16:31
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I'm not sure what you mean by posting individual batches one at a time.  Is it only sales history and not PO receipt history?  If so, why not import to SO_invoiceHeader and SO_InvoiceDetail in a test company copy of live, update,then copy IM_ItemTransactionHistory back to the live company?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------


Original Message:
Sent: 03-26-2021 16:27
From: Robert White
Subject: Importing into IM_ItemTransactionHistory

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

I didn't think Sage was using buckets anymore.  I thought you could put all of the invoices for every date / month in one batch and update it and the reports would select by invoice date. That's a huge inconvenience if you have to have separate batched for each month.  Of course the GL posting is always in the same month but you most likely don't care about the GL posting for your purposes.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

Original Message:
Sent: 03-29-2021 09:17
From: alan niergarth
Subject: Importing into IM_ItemTransactionHistory

@Doug Higgs Seems like this would work cleanly.  Except for getting the sales into monthly buckets in period summary posting.  You could do a crystal report to sort sales by invoice date from invoice history.  ​

------------------------------
alan niergarth
Velosio LLC
------------------------------


Original Message:
Sent: 03-26-2021 16:31
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I'm not sure what you mean by posting individual batches one at a time.  Is it only sales history and not PO receipt history?  If so, why not import to SO_invoiceHeader and SO_InvoiceDetail in a test company copy of live, update,then copy IM_ItemTransactionHistory back to the live company?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-26-2021 16:27
From: Robert White
Subject: Importing into IM_ItemTransactionHistory

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

"Buckets" are Item/FiscalYear/Period buckets in CI_ItemHistoryByPeriod file.

------------------------------
Lee Graham
Friendly Systems, Inc.
------------------------------

Original Message:
Sent: 03-29-2021 09:45
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I didn't think Sage was using buckets anymore.  I thought you could put all of the invoices for every date / month in one batch and update it and the reports would select by invoice date. That's a huge inconvenience if you have to have separate batched for each month.  Of course the GL posting is always in the same month but you most likely don't care about the GL posting for your purposes.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------


Original Message:
Sent: 03-29-2021 09:17
From: alan niergarth
Subject: Importing into IM_ItemTransactionHistory

@Doug Higgs Seems like this would work cleanly.  Except for getting the sales into monthly buckets in period summary posting.  You could do a crystal report to sort sales by invoice date from invoice history.  ​

------------------------------
alan niergarth
Velosio LLC


Original Message:
Sent: 03-26-2021 16:31
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I'm not sure what you mean by posting individual batches one at a time.  Is it only sales history and not PO receipt history?  If so, why not import to SO_invoiceHeader and SO_InvoiceDetail in a test company copy of live, update,then copy IM_ItemTransactionHistory back to the live company?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-26-2021 16:27
From: Robert White
Subject: Importing into IM_ItemTransactionHistory

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

I haven't tested it but I am sure you are probably correct that the invoices need to be sorted by period into batches.  That;s too bad. How hard would it be to put code in the sales journal update to read the invoice date and then write the amount to the correct "bucket"?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

Original Message:
Sent: 03-29-2021 09:57
From: Lee Graham
Subject: Importing into IM_ItemTransactionHistory

"Buckets" are Item/FiscalYear/Period buckets in CI_ItemHistoryByPeriod file.

------------------------------
Lee Graham
Friendly Systems, Inc.
------------------------------


Original Message:
Sent: 03-29-2021 09:45
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I didn't think Sage was using buckets anymore.  I thought you could put all of the invoices for every date / month in one batch and update it and the reports would select by invoice date. That's a huge inconvenience if you have to have separate batched for each month.  Of course the GL posting is always in the same month but you most likely don't care about the GL posting for your purposes.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-29-2021 09:17
From: alan niergarth
Subject: Importing into IM_ItemTransactionHistory

@Doug Higgs Seems like this would work cleanly.  Except for getting the sales into monthly buckets in period summary posting.  You could do a crystal report to sort sales by invoice date from invoice history.  ​

------------------------------
alan niergarth
Velosio LLC


Original Message:
Sent: 03-26-2021 16:31
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I'm not sure what you mean by posting individual batches one at a time.  Is it only sales history and not PO receipt history?  If so, why not import to SO_invoiceHeader and SO_InvoiceDetail in a test company copy of live, update,then copy IM_ItemTransactionHistory back to the live company?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-26-2021 16:27
From: Robert White
Subject: Importing into IM_ItemTransactionHistory

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

@Lee Graham  The table is IM_PeriodPostingHistory not CI_ItemHistoryByPeriod.  It look slike CI_ItemHistoryByPersiod is for miscellaneous and special items.  I tested this and you and @alan niergarth are correct, it doesn't matter what the invoice date is the data gets written to one period.  What century are we living in?  It's bush league 1980's SaaS programming. ​​​

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

Original Message:
Sent: 03-29-2021 09:57
From: Lee Graham
Subject: Importing into IM_ItemTransactionHistory

"Buckets" are Item/FiscalYear/Period buckets in CI_ItemHistoryByPeriod file.

------------------------------
Lee Graham
Friendly Systems, Inc.
------------------------------


Original Message:
Sent: 03-29-2021 09:45
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I didn't think Sage was using buckets anymore.  I thought you could put all of the invoices for every date / month in one batch and update it and the reports would select by invoice date. That's a huge inconvenience if you have to have separate batched for each month.  Of course the GL posting is always in the same month but you most likely don't care about the GL posting for your purposes.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-29-2021 09:17
From: alan niergarth
Subject: Importing into IM_ItemTransactionHistory

@Doug Higgs Seems like this would work cleanly.  Except for getting the sales into monthly buckets in period summary posting.  You could do a crystal report to sort sales by invoice date from invoice history.  ​

------------------------------
alan niergarth
Velosio LLC


Original Message:
Sent: 03-26-2021 16:31
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I'm not sure what you mean by posting individual batches one at a time.  Is it only sales history and not PO receipt history?  If so, why not import to SO_invoiceHeader and SO_InvoiceDetail in a test company copy of live, update,then copy IM_ItemTransactionHistory back to the live company?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-26-2021 16:27
From: Robert White
Subject: Importing into IM_ItemTransactionHistory

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

Thanks everyone for your input.  Although these other imports will get me the same end result ... eventually, importing directly into the original table is what I was hoping for.  It's been confirmed that it's set programmatically by the business object and is not modifiable by a back door hack.

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-29-2021 10:17
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

@Lee Graham  The table is IM_PeriodPostingHistory not CI_ItemHistoryByPeriod.  It look slike CI_ItemHistoryByPersiod is for miscellaneous and special items.  I tested this and you and @alan niergarth are correct, it doesn't matter what the invoice date is the data gets written to one period.  What century are we living in?  It's bush league 1980's SaaS programming. ​​​

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------


Original Message:
Sent: 03-29-2021 09:57
From: Lee Graham
Subject: Importing into IM_ItemTransactionHistory

"Buckets" are Item/FiscalYear/Period buckets in CI_ItemHistoryByPeriod file.

------------------------------
Lee Graham
Friendly Systems, Inc.


Original Message:
Sent: 03-29-2021 09:45
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I didn't think Sage was using buckets anymore.  I thought you could put all of the invoices for every date / month in one batch and update it and the reports would select by invoice date. That's a huge inconvenience if you have to have separate batched for each month.  Of course the GL posting is always in the same month but you most likely don't care about the GL posting for your purposes.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-29-2021 09:17
From: alan niergarth
Subject: Importing into IM_ItemTransactionHistory

@Doug Higgs Seems like this would work cleanly.  Except for getting the sales into monthly buckets in period summary posting.  You could do a crystal report to sort sales by invoice date from invoice history.  ​

------------------------------
alan niergarth
Velosio LLC


Original Message:
Sent: 03-26-2021 16:31
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

I'm not sure what you mean by posting individual batches one at a time.  Is it only sales history and not PO receipt history?  If so, why not import to SO_invoiceHeader and SO_InvoiceDetail in a test company copy of live, update,then copy IM_ItemTransactionHistory back to the live company?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary


Original Message:
Sent: 03-26-2021 16:27
From: Robert White
Subject: Importing into IM_ItemTransactionHistory

Thanks for the idea, but I need to do this for 5 years of history.  I can't post individual batches back for 5 years one month at a time and I don't think you can identify a customer in that transaction type.  Ideally, we want to know who bought what in each month for 5 years back

I would absolutely have done it via SQL if I could but this new client is our first in the Sage Partner Cloud and Sage 100 Standard it is!

------------------------------
Robert White
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-26-2021 16:18
From: Doug Higgs
Subject: Importing into IM_ItemTransactionHistory

Can you import to IM_TransctionHeader and IM_TransactionDetail then update, which would populate IM_ItemTransactionHistpry?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

Robert the cannot add message is coming from the business object itself. Although you can tweak the dictionary to make some Read Only fields available for import, you cannot add new records, only edit existing ones. Ergo you would need a programming change to remove the restriction.

---------------------------------
Alnoor Cassim


Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
---------------------------------

Alnoor,
Does that mean we couldn't import to somewhere like a UDT, and use a BOI script to copy into IM_ItemTransactionHistory?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-27-2021 10:32
From: Alnoor Cassim
Subject: Importing into IM_ItemTransactionHistory

Robert the cannot add message is coming from the business object itself. Although you can tweak the dictionary to make some Read Only fields available for import, you cannot add new records, only edit existing ones. Ergo you would need a programming change to remove the restriction.

---------------------------------
Alnoor Cassim


Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
---------------------------------

Kevin that is a creative idea. But in the BOI it would run into the same IM_ItemTransactionHistory_bus restriction of not being able to add new records, assuming I'm understanding your idea correctly.

---------------------------------
Alnoor Cassim


Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
---------------------------------



Original Message:
Sent: 03-27-2021
From: Kevin Moyes
Subject: RE: Importing into IM_ItemTransactionHistory

Alnoor,
Does that mean we couldn't import to somewhere like a UDT, and use a BOI script to copy into IM_ItemTransactionHistory?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------