Sage 100

Anybody know if it is possible to export a header record from Sales Order and the detail records separately. I know we can import the header/detail separate, but I don't see a way to export??? Perform Logic maybe? 

It would need to look something like this,
Thanks in Advance for any ideas!!

"PH","2016981433","12345678","JOAN HARRY","5050 Road N","","Waterloo","ON","N1N 1N1","CA","","555-666-7777" 
"PD","2016981433","1","13358A","16"
"PD","2016981433","2","2564A","6"
"PD","2016981433","3","568955","6"
"PD","2016981433","4","5666","6"
"PD","2016981433","5","5665","4"

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Never had to figure that out so don't have a definitive answer but I have to ask why one of the existing sales order forms, reports, or custom report can't be used by designing the report in Crystal to export cleanly to CSV, tab, or excel.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posItemCode = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posItemCode {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posItemCode),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posItemCode - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posItemCode),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA
------------------------------

Thanks @Alnoor Cassim! I will give this a shot. 

@David Speck II This is a PERFECT case for Sage Alerts, but the client has decided it's not a good time to implement something new. ​​​It is to send the Open Orders to a 3PL company for shipping purposes. They want completely hands off, and the 3PL has a very specific format that they (of course) will not budge on.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posSO = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posSO {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posSO - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA
------------------------------

If it is one order at a time, I've done that in a button script (get the data, string together the output however you want, then write to a text file).

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

Original Message:
Sent: 08-07-2020 11:11
From: Michelle Forsey
Subject: Export Header and Detail separate?

Thanks @Alnoor Cassim! I will give this a shot.

@David Speck II This is a PERFECT case for Sage Alerts, but the client has decided it's not a good time to implement something new. ​​​It is to send the Open Orders to a 3PL company for shipping purposes. They want completely hands off, and the 3PL has a very specific format that they (of course) will not budge on.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------


Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posSO = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posSO {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posSO - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA

THAT is a great idea for another client I have! (THANKS!!)

This one is I think every 30 or 60 minutes, and it exports about 1000 orders a day.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Original Message:
Sent: 08-07-2020 11:14
From: Kevin Moyes
Subject: Export Header and Detail separate?

If it is one order at a time, I've done that in a button script (get the data, string together the output however you want, then write to a text file).

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


Original Message:
Sent: 08-07-2020 11:11
From: Michelle Forsey
Subject: Export Header and Detail separate?

Thanks @Alnoor Cassim! I will give this a shot.

@David Speck II This is a PERFECT case for Sage Alerts, but the client has decided it's not a good time to implement something new. ​​​It is to send the Open Orders to a 3PL company for shipping purposes. They want completely hands off, and the 3PL has a very specific format that they (of course) will not budge on.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posSO = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posSO {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posSO - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA

You can also script an ODBC query and schedule that to export in bulk, but then you have potential issues with repeats.

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

Original Message:
Sent: 08-07-2020 11:16
From: Michelle Forsey
Subject: Export Header and Detail separate?

THAT is a great idea for another client I have! (THANKS!!)

This one is I think every 30 or 60 minutes, and it exports about 1000 orders a day.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------


Original Message:
Sent: 08-07-2020 11:14
From: Kevin Moyes
Subject: Export Header and Detail separate?

If it is one order at a time, I've done that in a button script (get the data, string together the output however you want, then write to a text file).

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


Original Message:
Sent: 08-07-2020 11:11
From: Michelle Forsey
Subject: Export Header and Detail separate?

Thanks @Alnoor Cassim! I will give this a shot.

@David Speck II This is a PERFECT case for Sage Alerts, but the client has decided it's not a good time to implement something new. ​​​It is to send the Open Orders to a 3PL company for shipping purposes. They want completely hands off, and the 3PL has a very specific format that they (of course) will not budge on.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posSO = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posSO {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posSO - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA

@Michelle Forsey - following this thread to see if that worked for you.   ​

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

Original Message:
Sent: 08-07-2020 11:16
From: Michelle Forsey
Subject: Export Header and Detail separate?

THAT is a great idea for another client I have! (THANKS!!)

This one is I think every 30 or 60 minutes, and it exports about 1000 orders a day.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------


Original Message:
Sent: 08-07-2020 11:14
From: Kevin Moyes
Subject: Export Header and Detail separate?

If it is one order at a time, I've done that in a button script (get the data, string together the output however you want, then write to a text file).

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


Original Message:
Sent: 08-07-2020 11:11
From: Michelle Forsey
Subject: Export Header and Detail separate?

Thanks @Alnoor Cassim! I will give this a shot.

@David Speck II This is a PERFECT case for Sage Alerts, but the client has decided it's not a good time to implement something new. ​​​It is to send the Open Orders to a 3PL company for shipping purposes. They want completely hands off, and the 3PL has a very specific format that they (of course) will not budge on.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posSO = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posSO {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posSO - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA

This could be done with an Excel VBA macro and an ADO query to the sales order header and detail tables.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Original Message:
Sent: 08-07-2020 16:41
From: alan niergarth
Subject: Export Header and Detail separate?

@Michelle Forsey - following this thread to see if that worked for you.   ​

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


Original Message:
Sent: 08-07-2020 11:16
From: Michelle Forsey
Subject: Export Header and Detail separate?

THAT is a great idea for another client I have! (THANKS!!)

This one is I think every 30 or 60 minutes, and it exports about 1000 orders a day.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-07-2020 11:14
From: Kevin Moyes
Subject: Export Header and Detail separate?

If it is one order at a time, I've done that in a button script (get the data, string together the output however you want, then write to a text file).

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


Original Message:
Sent: 08-07-2020 11:11
From: Michelle Forsey
Subject: Export Header and Detail separate?

Thanks @Alnoor Cassim! I will give this a shot.

@David Speck II This is a PERFECT case for Sage Alerts, but the client has decided it's not a good time to implement something new. ​​​It is to send the Open Orders to a 3PL company for shipping purposes. They want completely hands off, and the 3PL has a very specific format that they (of course) will not budge on.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posSO = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posSO {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posSO - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA

@Dan Burleson unfortunately, macros are not a language I speak! ​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Original Message:
Sent: 08-08-2020 01:12
From: Dan Burleson
Subject: Export Header and Detail separate?

This could be done with an Excel VBA macro and an ADO query to the sales order header and detail tables.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------


Original Message:
Sent: 08-07-2020 16:41
From: alan niergarth
Subject: Export Header and Detail separate?

@Michelle Forsey - following this thread to see if that worked for you.   ​

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


Original Message:
Sent: 08-07-2020 11:16
From: Michelle Forsey
Subject: Export Header and Detail separate?

THAT is a great idea for another client I have! (THANKS!!)

This one is I think every 30 or 60 minutes, and it exports about 1000 orders a day.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-07-2020 11:14
From: Kevin Moyes
Subject: Export Header and Detail separate?

If it is one order at a time, I've done that in a button script (get the data, string together the output however you want, then write to a text file).

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


Original Message:
Sent: 08-07-2020 11:11
From: Michelle Forsey
Subject: Export Header and Detail separate?

Thanks @Alnoor Cassim! I will give this a shot.

@David Speck II This is a PERFECT case for Sage Alerts, but the client has decided it's not a good time to implement something new. ​​​It is to send the Open Orders to a 3PL company for shipping purposes. They want completely hands off, and the 3PL has a very specific format that they (of course) will not budge on.

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posSO = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posSO {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posSO - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posSO),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA

@Alnoor Cassim you are a lifesaver!! This worked beautifully!! I had to tweak a few things and continue to tweak it as they test (and move the goal post), but all in all this a great solution!! ( @alan niergarth) ​​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Original Message:
Sent: 08-07-2020 03:45
From: Alnoor Cassim
Subject: Export Header and Detail separate?

Perform Logic required. Here is 1 way.
In Configuration tab choose SO_SalesOrderDetail as the table (not SO_SalesOrderHeader)
In Data tab add all the fields you need from SO_SalesOrderHeader first then from SO_SalesOrderDetail
Something like this:

Then on Configuration tab, click Perform button to add a Before Write event and point it to a text file that lives below \mas90.
Paste this in. We are manipulating the exp$ variable which is the data written to the target file.
In this example, I'm assuming ItemCode is 1st Detail field being exported. Change if different for you.

%CurrentSO$ = SO_SalesOrderDetail01.SalesOrderNo$
posItemCode = POS(SO_SalesOrderDetail01.ItemCode$ = exp$)
IF posItemCode {
  IF %LastSO$ = %CurrentSO$ {
    !We are on same SalesOrderNo
    !WRITE Detail record only
    exp$ = "PD" + FieldDelimiter$ + STP(exp$(posItemCode),1,",")
  } ELSE {
     !WRITE Header fields plus Enter plus 1st Detail record
     exp$ = "PH" + FieldDelimiter$ + MID(exp$,1,posItemCode - 2) + \
     $0D$ + $0A$ + \
     "PD" + FieldDelimiter$ + STP(exp$(posItemCode),1,",")
     %LastSO$ = %CurrentSO$
  }
} 
EXIT

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

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA
------------------------------