BAQ/Dashboard - Remove Carriage Return

[system]

I use this in a calculated field for the RMADisp.reasoncode in a BAQ to replace the CR with //

replace(replace(HDCase.ResolutionText, chr(13), " // "), chr(10), " // ")
Â
This allows the ReasonCode to be copied and pasted into a single cell in an Excel spreadsheet. I haven't found a way to preserve the CR in a dashboard and still have it pasted into a single cell in Excel, but you can do a replace afterwards and replace all the // with a CR is you like.



________________________________
From: emailrashidforum <emailrashidforum@...>
To: vantage@yahoogroups.com
Sent: Tuesday, July 14, 2009 7:42:08 AM
Subject: [Vantage] Re: BAQ/Dashboard - Remove Carriage Return





I have tried all this Replace function but it doesnt work in dashboards, When I try to copy all from the BAQ and paste on Excel then it works fine but when I put this BAQ on a Dashboard and then Copy All from dashboard and paste in Excel CR and LF didnt work, Can any one help please. thanks

--- In vantage@yahoogroups .com, "matt_rodenbaugh" <mattr@...> wrote:
>
> To answer the previous question, I need to be able to copy all from a
> dashboard query view and paste into excel without the return
> carriages forcing new lines and splitting up records. The copy and
> paste process needs to be done in a non-technical manner (i.e. simple
> stupid).
>
> Using a modified version of what Dave provided, I was able to get the
> following calculated field to work:
>
> Replace(Part. PartDescription, CHR(10), "")
>
> Thanks to Dave and everyone else who contributed! !!
>
> -Matt
>
> Fleetwood Fixtures
>
> --- In vantage@yahoogroups .com, "Dave Olender" <dolender@> wrote:
> >
> > --- In vantage@yahoogroups .com, Robert Brown <robertb_versa@ >
> > wrote:
> >
> > Mr. Matt: I have been using the following code for some time now
> and
> > it works great. Just create a calculated field and change the
> > table/field names to suit:
> >
> > If INDEX(JobAsmbl. Description, CHR(10)) > 0 Then
> > Replace(JobAsmbl. Description, CHR(13) + CHR(10), " ") Else
> > Replace(JobAsmbl. Description, CHR(13), " ")
> >
> >
> > Later,
> >
> > Dave Olender
> > dolender@
>







[Non-text portions of this message have been removed]

[system]

Hey guys,

We're running Vantage 8.03.404B, Progress 10.1B, and for the life of
me, I cannot figure out how to remove the carriage returns from
Description fields such as Part.PartDescription. One of the reasons I
need this solution is so that users can copy and paste from dashboards
into excel without the carriage returns adding new rows and breaking up
the records.

Thanks in advance,

Matt
Fleetwood Fixtures

[system]

I would be interested in the solution too. My rudimentary solution at the moment is to run the parts through an access report (for extremely large reports, such as month ends, etc, which I only prepare) which somehow removes the carriage return, or rather inserts a special character in place of the newline which can be easily removed. Our back end is SQL.

Thanks,
Kunal



----- Original Message ----
From: matt_rodenbaugh <mattr@...>
To: vantage@yahoogroups.com
Sent: Wednesday, October 15, 2008 4:04:32 PM
Subject: [Vantage] BAQ/Dashboard - Remove Carriage Return


Hey guys,

We're running Vantage 8.03.404B, Progress 10.1B, and for the life of
me, I cannot figure out how to remove the carriage returns from
Description fields such as Part.PartDescriptio n. One of the reasons I
need this solution is so that users can copy and paste from dashboards
into excel without the carriage returns adding new rows and breaking up
the records.

Thanks in advance,

Matt
Fleetwood Fixtures






[Non-text portions of this message have been removed]

[system]

If you are copying single cells on at a time from a grid (Part.PartDescription I think you said) you can copy into excel by clicking on the destination cell and then going to the cell detail bar (above the worksheet) and doing your paste in there. Carriage returns won't be removed but the source grid cell info will then paste into the single selected cell without excel 'helping' by assuming the carriage returns are its cue to copy into multiple cells.

Why do you have carriage returns at all in description field? v8 textbox controls support word wrap so, if the text box is sized large enough, it will display quite a bit of info (with no carriage returns needed)?

(Might be worth scrubbing your data and reimporting with out the CRs.)

If it is entire grid rows you are copying, a trick is to use word (or even outlook as long as it using word in html mode as the email editor) and paste special as an html table.

That should preserve everything with no interference from imbedded CRs. Then format those table columns in word to be wordwrap & you can likely recopy result and past clean multirows into excel.

If it is a repeated exercise, vbscript it to make it a few invoked macro key strokes.

Rob

--- On Wed, 10/15/08, Kunal Ganguly <kunal_vantage@...> wrote:
From: Kunal Ganguly <kunal_vantage@...>
Subject: Re: [Vantage] BAQ/Dashboard - Remove Carriage Return
To: vantage@yahoogroups.com
Date: Wednesday, October 15, 2008, 5:20 PM











I would be interested in the solution too. My rudimentary solution at the moment is to run the parts through an access report (for extremely large reports, such as month ends, etc, which I only prepare) which somehow removes the carriage return, or rather inserts a special character in place of the newline which can be easily removed. Our back end is SQL.



Thanks,

Kunal



----- Original Message ----

From: matt_rodenbaugh <mattr@fleetwoodfixt ures.com>

To: vantage@yahoogroups .com

Sent: Wednesday, October 15, 2008 4:04:32 PM

Subject: [Vantage] BAQ/Dashboard - Remove Carriage Return



Hey guys,



We're running Vantage 8.03.404B, Progress 10.1B, and for the life of

me, I cannot figure out how to remove the carriage returns from

Description fields such as Part.PartDescriptio n. One of the reasons I

need this solution is so that users can copy and paste from dashboards

into excel without the carriage returns adding new rows and breaking up

the records.



Thanks in advance,



Matt

Fleetwood Fixtures



[Non-text portions of this message have been removed]

[system]

There is a replace function in progress that might work.You would have to use Chr(13) and replace it with a space. For Example:

Replace(Part.PartDescription,Chr(13)," ")

I tried using the calc field where I removed the carriage return to replace the line feeds, Chr(11), but was not successful in getting it to recognize the first calc field in the expression, eg. Replace(NewDesc1,Chr(11)," "). It will give a syntax error every time. I can't explain that one as normally you should be able to use one calc field in another.

It does not appear this is needed in version 8.03.405 and up as the carriage returns and line feeds are automatically removed in the query process. I just tested it with a dashboard that I have and the carriage returns and line feeds were automatically removed, however, they appear to be removed with no spaces so that the lines will just run together.
So if your had:

Don't remove this
one please

It would appear as Don't remove thisone please.

I don't have an earlier version to test this on but I think I have done this before. I just could not find the query that I was doing it in.

Give it a try and let me know if it works.

----- Original Message -----
From: matt_rodenbaugh
To: vantage@yahoogroups.com
Sent: Wednesday, October 15, 2008 4:04 PM
Subject: [Vantage] BAQ/Dashboard - Remove Carriage Return


Hey guys,

We're running Vantage 8.03.404B, Progress 10.1B, and for the life of
me, I cannot figure out how to remove the carriage returns from
Description fields such as Part.PartDescription. One of the reasons I
need this solution is so that users can copy and paste from dashboards
into excel without the carriage returns adding new rows and breaking up
the records.

Thanks in advance,

Matt
Fleetwood Fixtures





[Non-text portions of this message have been removed]

[system]

Thanks for the responses!

I attempted to use the Replace function in a calculated field putting
both:
Replace(Part.PartDescription,Chr(13),CHR(32))
and
Replace(Part.PartDescription,Chr(13)," ")

The syntax returned OK for both, but the desired results did not
occur. The Carriage Returns were still in the field and Excel still
split the records. Using Excel we're able to use Formulas and create
pivot tables with the data.

Are there any other suggestions?

Thanks again,

Matt
Fleetwood Fixtures


--- In vantage@yahoogroups.com, "Charles Carden" <shadowcar1449@...>
wrote:

>
> There is a replace function in progress that might work.You would

have to use Chr(13) and replace it with a space. For Example:

>
> Replace(Part.PartDescription,Chr(13)," ")
>
> I tried using the calc field where I removed the carriage return to

replace the line feeds, Chr(11), but was not successful in getting it
to recognize the first calc field in the expression, eg. Replace
(NewDesc1,Chr(11)," "). It will give a syntax error every time. I
can't explain that one as normally you should be able to use one calc
field in another.

>
> It does not appear this is needed in version 8.03.405 and up as the

carriage returns and line feeds are automatically removed in the
query process. I just tested it with a dashboard that I have and the
carriage returns and line feeds were automatically removed, however,
they appear to be removed with no spaces so that the lines will just
run together.

> So if your had:
>
> Don't remove this
> one please
>
> It would appear as Don't remove thisone please.
>
> I don't have an earlier version to test this on but I think I have

done this before. I just could not find the query that I was doing
it in.

>
> Give it a try and let me know if it works.

[system]

Have you looked at the data in a text/hex editor?
You could have LineFeeds in there Chr(10)

--- In vantage@yahoogroups.com, "matt_rodenbaugh" <mattr@...> wrote:
>
> Thanks for the responses!
>
> I attempted to use the Replace function in a calculated field
putting
> both:
> Replace(Part.PartDescription,Chr(13),CHR(32))
> and
> Replace(Part.PartDescription,Chr(13)," ")
>
> The syntax returned OK for both, but the desired results did not
> occur. The Carriage Returns were still in the field and Excel
still
> split the records. Using Excel we're able to use Formulas and
create
> pivot tables with the data.
>
> Are there any other suggestions?
>
> Thanks again,
>
> Matt
> Fleetwood Fixtures
>
>
> --- In vantage@yahoogroups.com, "Charles Carden" <shadowcar1449@>
> wrote:
> >
> > There is a replace function in progress that might work.You would
> have to use Chr(13) and replace it with a space. For Example:
> >
> > Replace(Part.PartDescription,Chr(13)," ")
> >
> > I tried using the calc field where I removed the carriage return
to
> replace the line feeds, Chr(11), but was not successful in getting
it
> to recognize the first calc field in the expression, eg. Replace
> (NewDesc1,Chr(11)," "). It will give a syntax error every time. I
> can't explain that one as normally you should be able to use one
calc
> field in another.
> >
> > It does not appear this is needed in version 8.03.405 and up as
the
> carriage returns and line feeds are automatically removed in the
> query process. I just tested it with a dashboard that I have and
the
> carriage returns and line feeds were automatically removed,
however,
> they appear to be removed with no spaces so that the lines will
just
> run together.
> > So if your had:
> >
> > Don't remove this
> > one please
> >
> > It would appear as Don't remove thisone please.
> >
> > I don't have an earlier version to test this on but I think I
have
> done this before. I just could not find the query that I was doing
> it in.
> >
> > Give it a try and let me know if it works.
>

[system]

Matt, try the same thing with chr(10), the linefeed character. Remembering from days long gone by that a carriage return was usually delimited as a carriagereturn/linefeed combo.
Â
-Karl

--- On Thu, 10/16/08, matt_rodenbaugh <mattr@...> wrote:

From: matt_rodenbaugh <mattr@...>
Subject: [Vantage] Re: BAQ/Dashboard - Remove Carriage Return
To: vantage@yahoogroups.com
Date: Thursday, October 16, 2008, 1:10 PM






Thanks for the responses!

I attempted to use the Replace function in a calculated field putting
both:
Replace(Part. PartDescription, Chr(13),CHR( 32))
and
Replace(Part. PartDescription, Chr(13)," ")

The syntax returned OK for both, but the desired results did not
occur. The Carriage Returns were still in the field and Excel still
split the records. Using Excel we're able to use Formulas and create
pivot tables with the data.

Are there any other suggestions?

Thanks again,

Matt
Fleetwood Fixtures

--- In vantage@yahoogroups .com, "Charles Carden" <shadowcar1449@ ...>
wrote:
>
> There is a replace function in progress that might work.You would
have to use Chr(13) and replace it with a space. For Example:
>
> Replace(Part. PartDescription, Chr(13)," ")
>
> I tried using the calc field where I removed the carriage return to
replace the line feeds, Chr(11), but was not successful in getting it
to recognize the first calc field in the expression, eg. Replace
(NewDesc1,Chr( 11)," "). It will give a syntax error every time. I
can't explain that one as normally you should be able to use one calc
field in another.
>
> It does not appear this is needed in version 8.03.405 and up as the
carriage returns and line feeds are automatically removed in the
query process. I just tested it with a dashboard that I have and the
carriage returns and line feeds were automatically removed, however,
they appear to be removed with no spaces so that the lines will just
run together.
> So if your had:
>
> Don't remove this
> one please
>
> It would appear as Don't remove thisone please.
>
> I don't have an earlier version to test this on but I think I have
done this before. I just could not find the query that I was doing
it in.
>
> Give it a try and let me know if it works.















__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]

[system]

Refresh my memory Matt (as the email thread has been broken).

What version are you running?

Are you doing a copy all from some grid view and then attempting to paste into excel?

(If not, exactly what are you doing? What App?, What Tab on the App form?)

Have you tried doing a simple odbc SQL query to pull your data into excel?

Have you tried writing a simple BAQ and then exporting it to xml or csv (and then using THAT as a data source for excel)?

Even w/ imbedded carriage returns, the contents of Part.PartDescription for each Part.PartNum is still a single record.

Excel's penchant for 'helping' is to interpret the CRs as its cue to split the copied record data into multiple cells.

Excel (5.0 - 2003) will NOT do that if you grab the data via odbc/SQL or BAQ export to xml or csv (and using the xml or csv file as an excel data source).

Once in excel, you can use it to remove the carriage returns and let word wrap take over.

If you try BAQ export, I suggest exporting to csv 1st (as xml can produce ugly excel query results unless you want to deal with style sheets).

The csv files are also at least 1/10th the size of the equivalent xml file - the price of 'progress' in the computing world ;{

Rob

--- On Thu, 10/16/08, matt_rodenbaugh <mattr@...> wrote:
From: matt_rodenbaugh <mattr@...>
Subject: [Vantage] Re: BAQ/Dashboard - Remove Carriage Return
To: vantage@yahoogroups.com
Date: Thursday, October 16, 2008, 4:10 PM











Thanks for the responses!



I attempted to use the Replace function in a calculated field putting

both:

Replace(Part. PartDescription, Chr(13),CHR( 32))

and

Replace(Part. PartDescription, Chr(13)," ")



The syntax returned OK for both, but the desired results did not

occur. The Carriage Returns were still in the field and Excel still

split the records. Using Excel we're able to use Formulas and create

pivot tables with the data.



Are there any other suggestions?



Thanks again,



Matt

Fleetwood Fixtures



--- In vantage@yahoogroups .com, "Charles Carden" <shadowcar1449@ ...>

wrote:

>

> There is a replace function in progress that might work.You would

have to use Chr(13) and replace it with a space. For Example:

>

> Replace(Part. PartDescription, Chr(13)," ")

>

> I tried using the calc field where I removed the carriage return to

replace the line feeds, Chr(11), but was not successful in getting it

to recognize the first calc field in the expression, eg. Replace

(NewDesc1,Chr( 11)," "). It will give a syntax error every time. I

can't explain that one as normally you should be able to use one calc

field in another.

>

> It does not appear this is needed in version 8.03.405 and up as the

carriage returns and line feeds are automatically removed in the

query process. I just tested it with a dashboard that I have and the

carriage returns and line feeds were automatically removed, however,

they appear to be removed with no spaces so that the lines will just

run together.

> So if your had:

>

> Don't remove this

> one please

>

> It would appear as Don't remove thisone please.

>

> I don't have an earlier version to test this on but I think I have

done this before. I just could not find the query that I was doing

it in.

>

> Give it a try and let me know if it works.


























__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[system]

--- In vantage@yahoogroups.com, Robert Brown <robertb_versa@...>
wrote:

Mr. Matt: I have been using the following code for some time now and
it works great. Just create a calculated field and change the
table/field names to suit:

If INDEX(JobAsmbl.Description, CHR(10)) > 0 Then
Replace(JobAsmbl.Description, CHR(13) + CHR(10), " ") Else
Replace(JobAsmbl.Description, CHR(13), " ")


Later,

Dave Olender
dolender@...

>
> Refresh my memory Matt (as the email thread has been broken).
>
> What version are you running?
>
> Are you doing a copy all from some grid view and then attempting to

paste into excel?

>
> (If not, exactly what are you doing? What App?, What Tab on the App

form?)

>
> Have you tried doing a simple odbc SQL query to pull your data into

excel?

>
> Have you tried writing a simple BAQ and then exporting it to xml or

csv (and then using THAT as a data source for excel)?

>
> Even w/ imbedded carriage returns, the contents of

Part.PartDescription for each Part.PartNum is still a single record.

>
> Excel's penchant for 'helping' is to interpret the CRs as its cue

to split the copied record data into multiple cells.

>
> Excel (5.0 - 2003) will NOT do that if you grab the data via

odbc/SQL or BAQ export to xml or csv (and using the xml or csv file
as an excel data source).

>
> Once in excel, you can use it to remove the carriage returns and

let word wrap take over.

>
> If you try BAQ export, I suggest exporting to csv 1st (as xml can

produce ugly excel query results unless you want to deal with style
sheets).

>
> The csv files are also at least 1/10th the size of the equivalent

xml file - the price of 'progress' in the computing world ;{

>
> Rob
>
>
> --- On Thu, 10/16/08, matt_rodenbaugh <mattr@...> wrote:
> From: matt_rodenbaugh <mattr@...>
> Subject: [Vantage] Re: BAQ/Dashboard - Remove Carriage Return
> To: vantage@yahoogroups.com
> Date: Thursday, October 16, 2008, 4:10 PM
>
>
>
>
>
>
>
>
>
>
>
> Thanks for the responses!
>
>
>
> I attempted to use the Replace function in a calculated field

putting

>
> both:
>
> Replace(Part. PartDescription, Chr(13),CHR( 32))
>
> and
>
> Replace(Part. PartDescription, Chr(13)," ")
>
>
>
> The syntax returned OK for both, but the desired results did not
>
> occur. The Carriage Returns were still in the field and Excel

still

>
> split the records. Using Excel we're able to use Formulas and

create

>
> pivot tables with the data.
>
>
>
> Are there any other suggestions?
>
>
>
> Thanks again,
>
>
>
> Matt
>
> Fleetwood Fixtures
>
>
>
> --- In vantage@yahoogroups .com, "Charles Carden"

<shadowcar1449@ ...>

>
> wrote:
>
> >
>
> > There is a replace function in progress that might work.You would
>
> have to use Chr(13) and replace it with a space. For Example:
>
> >
>
> > Replace(Part. PartDescription, Chr(13)," ")
>
> >
>
> > I tried using the calc field where I removed the carriage return

to

>
> replace the line feeds, Chr(11), but was not successful in getting

it

>
> to recognize the first calc field in the expression, eg. Replace
>
> (NewDesc1,Chr( 11)," "). It will give a syntax error every time.

I

>
> can't explain that one as normally you should be able to use one

calc

>
> field in another.
>
> >
>
> > It does not appear this is needed in version 8.03.405 and up as

the

>
> carriage returns and line feeds are automatically removed in the
>
> query process. I just tested it with a dashboard that I have and

the

>
> carriage returns and line feeds were automatically removed,

however,

>
> they appear to be removed with no spaces so that the lines will

just

>
> run together.
>
> > So if your had:
>
> >
>
> > Don't remove this
>
> > one please
>
> >
>
> > It would appear as Don't remove thisone please.
>
> >
>
> > I don't have an earlier version to test this on but I think I

have

>
> done this before. I just could not find the query that I was doing
>
> it in.
>
> >
>
> > Give it a try and let me know if it works.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

[system]

To answer the previous question, I need to be able to copy all from a
dashboard query view and paste into excel without the return
carriages forcing new lines and splitting up records. The copy and
paste process needs to be done in a non-technical manner (i.e. simple
stupid).

Using a modified version of what Dave provided, I was able to get the
following calculated field to work:

Replace(Part.PartDescription, CHR(10), "")

Thanks to Dave and everyone else who contributed!!!

-Matt

Fleetwood Fixtures

--- In vantage@yahoogroups.com, "Dave Olender" <dolender@...> wrote:
>
> --- In vantage@yahoogroups.com, Robert Brown <robertb_versa@>
> wrote:
>
> Mr. Matt: I have been using the following code for some time now
and
> it works great. Just create a calculated field and change the
> table/field names to suit:
>
> If INDEX(JobAsmbl.Description, CHR(10)) > 0 Then
> Replace(JobAsmbl.Description, CHR(13) + CHR(10), " ") Else
> Replace(JobAsmbl.Description, CHR(13), " ")
>
>
> Later,
>
> Dave Olender
> dolender@...

[system]

I have tried all this Replace function but it doesnt work in dashboards, When I try to copy all from the BAQ and paste on Excel then it works fine but when I put this BAQ on a Dashboard and then Copy All from dashboard and paste in Excel CR and LF didnt work, Can any one help please. thanks

--- In vantage@yahoogroups.com, "matt_rodenbaugh" <mattr@...> wrote:
>
> To answer the previous question, I need to be able to copy all from a
> dashboard query view and paste into excel without the return
> carriages forcing new lines and splitting up records. The copy and
> paste process needs to be done in a non-technical manner (i.e. simple
> stupid).
>
> Using a modified version of what Dave provided, I was able to get the
> following calculated field to work:
>
> Replace(Part.PartDescription, CHR(10), "")
>
> Thanks to Dave and everyone else who contributed!!!
>
> -Matt
>
> Fleetwood Fixtures
>
> --- In vantage@yahoogroups.com, "Dave Olender" <dolender@> wrote:
> >
> > --- In vantage@yahoogroups.com, Robert Brown <robertb_versa@>
> > wrote:
> >
> > Mr. Matt: I have been using the following code for some time now
> and
> > it works great. Just create a calculated field and change the
> > table/field names to suit:
> >
> > If INDEX(JobAsmbl.Description, CHR(10)) > 0 Then
> > Replace(JobAsmbl.Description, CHR(13) + CHR(10), " ") Else
> > Replace(JobAsmbl.Description, CHR(13), " ")
> >
> >
> > Later,
> >
> > Dave Olender
> > dolender@
>