Integration manager - parsing row source

This may actually be an Excel question but, I'm trying to import SOP 
transactions with multiple items from a single source file.  The source file 
has one row per transaction with the various items I need included as columns 
within that row (unfortunately I can't change the format of the source file). 
 Each order can contain up to 4 different items (but never more than 4).

I don't know of a way to make IM read multiple versions of the source for 
each potential SOP line and haven't been able to come up with another 
approach so far, other than manually re-formating the source file so that 
each item on an order appears as a separate row.  Any other suggestions on 
how to approach this problem?  We're on version 9.  Thanks.
-- 
Jim@TurboChef
0
jim.harris (398)
12/7/2007 4:56:01 PM
greatplains 29623 articles. 6 followers. Follow

5 Replies
643 Views

Similar Articles

[PageSpeed] 50

there are two ways to do this:
1. use vbscript in the before integration event to programmatically format 
your source file into a new file
(your original can stay inviolate). or
2. Use an advanced odbc source to your text file and create union queries to 
the same source file.  here is an example
http://www.dynextra.com/news.asp?news_id=46
HS


"Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
news:FEDD11AA-E376-444E-B96B-9E0C5FEFC884@microsoft.com...
> This may actually be an Excel question but, I'm trying to import SOP
> transactions with multiple items from a single source file.  The source 
> file
> has one row per transaction with the various items I need included as 
> columns
> within that row (unfortunately I can't change the format of the source 
> file).
> Each order can contain up to 4 different items (but never more than 4).
>
> I don't know of a way to make IM read multiple versions of the source for
> each potential SOP line and haven't been able to come up with another
> approach so far, other than manually re-formating the source file so that
> each item on an order appears as a separate row.  Any other suggestions on
> how to approach this problem?  We're on version 9.  Thanks.
> -- 
> Jim@TurboChef 


0
HSalim (1270)
12/7/2007 5:20:58 PM
------=_NextPart_0001_4F9D5D65
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hello,

Since Integration Manager reads the data row by row it doesn't know that 
there could be multiple items as you can only map to one column for the 
Item Number. You would need to have a row for each particular Item Number 
that way Integration Manager would be able to read the data and pull in the 
correct information for the particular line item.


Thanks
Mitch
Developer Support
------=_NextPart_0001_4F9D5D65
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hello,
\par 
\par Since Integration Manager reads the data row by row it doesn't know that there could be multiple items as you can only map to one column for the Item Number. You would need to have a row for each particular Item Number that way Integration Manager would be able to read the data and pull in the correct information for the particular line item.
\par 
\par 
\par Thanks
\par Mitch
\par Developer Support
\par }
------=_NextPart_0001_4F9D5D65--

0
dclauson (5)
12/7/2007 10:17:27 PM
Here is how I've done this: create *two* Source queries pointing to the same 
Excel file.  I assume each Excel row includes both header and line values.  
In the 'header' query include only the header columns, and have a "Group By" 
on all of them (see the 'Sorting' tab in the Source Properties window).  In 
the "line" query include all the line item fields you need plus whatever 
header fields you need to support the relationship to the header query.  This 
method is simple and works great.  I hope I have understood what you are 
after.

"Jim@TurboChef" wrote:

> This may actually be an Excel question but, I'm trying to import SOP 
> transactions with multiple items from a single source file.  The source file 
> has one row per transaction with the various items I need included as columns 
> within that row (unfortunately I can't change the format of the source file). 
>  Each order can contain up to 4 different items (but never more than 4).
> 
> I don't know of a way to make IM read multiple versions of the source for 
> each potential SOP line and haven't been able to come up with another 
> approach so far, other than manually re-formating the source file so that 
> each item on an order appears as a separate row.  Any other suggestions on 
> how to approach this problem?  We're on version 9.  Thanks.
> -- 
> Jim@TurboChef
0
12/10/2007 11:05:04 PM
------=_NextPart_0001_83B522CA
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hello Jim

Checking in to see if there is anything else we can do for you on this 
topic.
Please post again at your convenience and we will be here for you.

Have a great week!

 
Roxanne Berg

Microsoft Online Support Engineer

 

Get Secure! - www.microsoft.com/security

==============================================

PLEASE NOTE:  The partner managed newsgroups are provided 

to assist with break/fix issues and simple how to questions.   

 

We also love to hear your product feedback!  

Let us know what you think by posting 

    from the web interface: Partner Feedback 

    from your newsreader:  microsoft.private.directaccess.partnerfeedback. 

We look forward to hearing from you!

===================================================== 

When responding to posts, please "Reply to Group" via your newsreader 

so that others may learn and benefit from this issue. 

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights. 

======================================================
------=_NextPart_0001_83B522CA
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hello Jim
\par 
\par Checking in to see if there is anything else we can do for you on this topic.
\par Please post again at your convenience and we will be here for you.
\par 
\par Have a great week!
\par 
\par  
\par Roxanne Berg
\par 
\par Microsoft Online Support Engineer
\par 
\par  
\par 
\par Get Secure! - www.microsoft.com/security
\par 
\par ==============================================
\par 
\par PLEASE NOTE:  The partner managed newsgroups are provided 
\par 
\par to assist with break/fix issues and simple how to questions.   
\par 
\par  
\par 
\par We also love to hear your product feedback!  
\par 
\par Let us know what you think by posting 
\par 
\par     from the web interface: Partner Feedback 
\par 
\par     from your newsreader:  microsoft.private.directaccess.partnerfeedback. 
\par 
\par We look forward to hearing from you!
\par 
\par ===================================================== 
\par 
\par When responding to posts, please "Reply to Group" via your newsreader 
\par 
\par so that others may learn and benefit from this issue. 
\par 
\par ======================================================
\par 
\par This posting is provided "AS IS" with no warranties, and confers no rights. 
\par 
\par ======================================================
\par 
\par }
------=_NextPart_0001_83B522CA--

0
a-rober (48)
12/14/2007 8:07:29 PM
------=_NextPart_0001_880A5AFF
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hi there

Anything else we can do for you here on this query?

Please post back at your convenience and we will be here for you.
Thanks again for using the partner newsgroups!

Have a great week!

 
Roxanne Berg

Microsoft Online Support Engineer

 

Get Secure! - www.microsoft.com/security

==============================================

PLEASE NOTE:  The partner managed newsgroups are provided 

to assist with break/fix issues and simple how to questions.   

 

We also love to hear your product feedback!  

Let us know what you think by posting 

    from the web interface: Partner Feedback 

    from your newsreader:  microsoft.private.directaccess.partnerfeedback. 

We look forward to hearing from you!

===================================================== 

When responding to posts, please "Reply to Group" via your newsreader 

so that others may learn and benefit from this issue. 

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights. 

======================================================
------=_NextPart_0001_880A5AFF
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hi there
\par 
\par Anything else we can do for you here on this query?
\par 
\par Please post back at your convenience and we will be here for you.
\par Thanks again for using the partner newsgroups!
\par 
\par Have a great week!
\par 
\par  
\par Roxanne Berg
\par 
\par Microsoft Online Support Engineer
\par 
\par  
\par 
\par Get Secure! - www.microsoft.com/security
\par 
\par ==============================================
\par 
\par PLEASE NOTE:  The partner managed newsgroups are provided 
\par 
\par to assist with break/fix issues and simple how to questions.   
\par 
\par  
\par 
\par We also love to hear your product feedback!  
\par 
\par Let us know what you think by posting 
\par 
\par     from the web interface: Partner Feedback 
\par 
\par     from your newsreader:  microsoft.private.directaccess.partnerfeedback. 
\par 
\par We look forward to hearing from you!
\par 
\par ===================================================== 
\par 
\par When responding to posts, please "Reply to Group" via your newsreader 
\par 
\par so that others may learn and benefit from this issue. 
\par 
\par ======================================================
\par 
\par This posting is provided "AS IS" with no warranties, and confers no rights. 
\par 
\par ======================================================
\par 
\par }
------=_NextPart_0001_880A5AFF--

0
a-rober (48)
12/18/2007 9:15:19 PM
Reply:

Similar Artilces:

publishing customizations using deployment manager
When I publich customizations, it comes back saying publish completed with errors, servername: failed. I notice that when I publish the customizations, my web site stops. I have to manually start it and then I can go into CRM and my changes are there. This is what I get in app log: ProcessImportWorkItem Error : The root element is missing. , StackTrace : at System.Xml.XmlTextReader.Read() at System.Xml.XmlValidatingReader.ReadWithCollectTextToken() at System.Xml.XmlValidatingReader.Read() at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserve...

RMA ( Credit) Missing Line Items
This is a multi-part message in MIME format. ------=_NextPart_000_0056_01C87CA9.5D5D6C20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Two RMA documents were received and then set as "Ready to Close". Both = documents show, in GP, as "History" but missing the line items. Checked the SQL tables and both RMA documents are in the "open" tables = (SVC05000 and SVC05200) and the line items are in the tables and appear = to be linked to the header. =20 How do we get the line items to appear on the RMA docu...

Managed User #2
Testing to see if this setup allows me to be properly identified as a managed user. ...

Changing rows by Columns
Hi, I have a query. I need to show the result of this query changing rows by columns in the table of my report. How I can to make it in SSRS 2005? Best regards and thanks!! Julio I would T-SQL , a new PIVOT command does that "julito" <julito@discussions.microsoft.com> wrote in message news:E79F47EB-504E-494B-AA4F-EFEEFF510A8D@microsoft.com... > Hi, > > I have a query. I need to show the result of this query changing rows by > columns in the table of my report. > > How I can to make it in SSRS 2005? > > Best regards and thank...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 $1.00 013-001 -1 $1.00- 013-002 5 $25.00 013-007 3 $120.00 013-007 -3 $120.00- 022-001 12 $17.00 041-009 7 $251.00 041-009 -7 $251.00- 052-061 10 $30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

Adjacent Cell to Last Cell in a Row
I am creating two columns where data is continually added. In one column I have units either lbs or kgs listed. In the second column I have a price. I want to create a formula that will pick the last entry in the units column and depending on whether it was lbs or kgs multiply the number in the cell beside it in a further calculation lbs 3.34 kg 5.67 kg 4.50 lb 8.94 lbs 8.94 kg 5.82 lbs 4.33 lb 6.22 lbs 2.68 kg 7.48 In this case in the first example the formula...

Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a particular question extend from Q6 to Z505. I need to count the people who did not answer the question (that is, the people, entered in rows 6 to 505 that left columns Q through Z blank). I'm not sure how to do this. Can someone offer a suggestion? Hi You can use a formula like this in Column AA =IF(COUNTA(Q6:Z6)=0,1,"") copy down till AA505 You can use a simple Sum formula now to count the empty ones =SUM(AA6:AA505) -- Regards Ron de Bruin http://www.rondebruin.nl "crossingboston" <...

Excel sheet with numbers as column & row headers
I received an excel spreadsheet that has numbers as column headers (instead of letters) and numbers as row headers. How do you change the headers for the columns and rows. I always thought they were set. I have looked and looked and can't find any information on this. Thanks! Probably this way: Tools>options>general deselect R1C1 reference style Regards, Fred "cg-ksd" <cg-ksd@discussions.microsoft.com> wrote in message news:66540232-A6AD-4955-B1D7-C20BB0D2B939@microsoft.com... >I received an excel spreadsheet that has numbers as column header...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

Macro to insert to move values of a cell to next cell in the same row
Hello, I would like to know the Macro code for Moving the Values of a cell to next cell based upon values in a particular cell. Ex:- Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day) Jan-05 10 Feb-05 10 Based upon date in Col A i want move the value in col b and c to the next cell.Basically this for aging the items in the work sheet Hi There, Can anyone pls give the code for this query. Pls help ...

Pasting in Rows?
Hi. Would anyone be so kind as to enlighten me about a simple pastin procedure in excel? I have a list (which I copied from a vertica column) that I want to paste into a horizontal row in Excel. I jus don�t see an option for this and the data gets put in vertically. Ther are hundreds of cells to paste so I�m hopeful that someone with mor experience can shed some light on the situation. I had the same proble with Access. Thanks in advance for any help you can offer -- FattyLumpki ----------------------------------------------------------------------- FattyLumpkin's Profile: http://www.e...

Link Rows Between Worksheets
Hi- I am trying to link rows (that are infinitely long in terms of data) between two different worksheets so that I dont have to retype all of the data. (I want them to be active links, so that if the data changes, it will automatically update). Is there a way to do this? (The first worksheet is a master that has a whole lot of information, and I would like the other worksheets to reference rows from the original...) Thank you very very much.. In the other sheets you will have to set then up eg: in sheet2 select the cell you want then type an equals sign = got to master sheet and se...

Pressing Enter to skip rows
We have a pro-forma in Excel for clients. Anyway, the cells where the enter information from a drop-down list are three rows apart, goin down the page. Is there anyway for the client to press Enter just once and go thre rows down rather than pressing enter three times? I am aware that if the sheet is locked with the particular input cell unlocked then Tab will take you to the next box however, we need Ente to do the same. I imagine a macro could achieve this. Thanks in advance D -- DoctorWar ----------------------------------------------------------------------- DoctorWard's Profile...

Get External Data
I have a 2 column table which is likely to change length whenever the Refresh Data menu option is selected. The table links to another software package (SAGE). I don't have formulae in adjacent columns so my problem can not be solved by checking the "Fill Down formulas in columns adjacent to data" box. I do, however, have text in adjacent columns (which the user selects from an in-cell drop down list) which I need to remain aligned with the relevant row. The "insert entire rows for new data ..." option does not appear to do what it says! It only inserts rows in the...

Inserting rows into sheet that contains a formula
I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

Modal popup with gridview row detail
Hi! I've show many example but I don't know how to apply solutions to my code: I've a gridview with a list of records, I'd like using a detail button, open an ajax modal popup with the detail (for update) of the row selected <asp:GridView ID="gvElencoAttivita" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" onrowdatabound="gvElencoAttivita_RowDataBound" ShowFooter="true" AllowSorting="True" CellPadding="8" GridLines="Vertical"> <Columns> ...

how do automatically change entire row color when cell changes
Hi, is it possible to change the color of the row cell1 is in when cell1 changes to a specified text? See Help under Conditional Formatting -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "IOE" <IOE@discussions.microsoft.com> wrote in message news:91738C3F-3BEB-4EAE-8551-99E60D8E8A21@microsoft.com... > Hi, > is it possible to change the color of the row cell1 is in when cell1 > changes > to a specified text? Hi - select the row (e.g. row 1) - goto 'Format - Conditional Format' - enter the following formula =$A1="T...

Error when starting up Deployment Manager
When we start up the Deployment manager we get "The selected snap-in did not initialize properly". I know when we first installed the deployment manager came up fine. But shortly after we started getting that message. Your post to that questions was -"Are you logging in with Admin authority". And the answer is yes, I am logging in with Domain Administrator authority. I did notice that originally when we installed the application there was a DOS window that was open when Deployment manager was open, but that is no longer opened, when that software is started. Yes, I am ge...

Test managed user
Testing again to see if my set up is correct to identify me as managed user. ------=_NextPart_0001_26B25925 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello, This email came in under Open- Managed User. Please let me know if you have further questions. Thanks, Dana Dunlop Microsoft Online Support Engineer ====================================================== PLEASE NOTE: The partner managed newsgroups are provided to assist with break/fix issues and simple how to questions. We also love to hear your product feedback! Let us know what you think by posting ...

Apply Multicurrency Invoices in Bank Management
I oppened a ticket to know why I cant apply Multicurrency Invoices to payment transactions in Bank Managment as it is available in Payable Management. I've been suggested to post a suggestion for that. Simply, while doing a payment in Bank Management. If the Chequebook Currency is diffrenet than the invoices currencies that you are paying againist, then you can't apply these invoices. Thus, you cant see them in the remittance advice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestio...

In Excel 2002 there was an addin 'Financial Management'
Does anyone know where it is in 2003 ? I didn't look, but there are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe that's where you got it for xl2002?????? (Or maybe a search of Google would be in order.) Alans wrote: > > Does anyone know where it is in 2003 ? -- Dave Peterson Thanks Had trawled those with no success Will go round again Cheers Alan "Dave Peterson" wrote: > I didn't look, but there are tons of free templates at: > http://office.microsoft.com/en-us/default.aspx > > Maybe that's where you g...

Parsing Names in Excel
I have a list in Excel from A1:A100 with names like the ones mentioned below: Michelle Fooo (michelfoo@exchange.SASMOM.com) Myhaass Smith (mysmith@exchange.SASMOM.com) I want to parse them using string functions to the following format michelfoo@SASMOM.com mysmith@SASMOM.com Any ideas how to do this using a formula? Thanks in advance Michael Michael Are you wanting to strip all the name out too or just remove the exchange.? If the latter just use edit>replace What:=exchange. and leave the with blank -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_...

Mailbox Manager
I tried posting this to a different group and got nothing back... I've got a single server, single storage group Exchange 2003. I have 4 Recipient Policies: Pri 1: main domain Pri 2: other domains Pri 3: Mailbox Cleanup Lowest: Default Policy The priority 3 policy (Mailbox Cleanup) is the only one that has Mailbox Manager Settings. I have constructed a query as follows: Find: "Exchange Recipients" General Tab: Show these recipients: "Users with Exchange mailbox" Storage Tab: "Mailboxes on any server" Advanced Tab: Condition List: "Member Of Is no ...

Excel Spreadsheets
Can someone help? This is the first time I have asked for help. I am new to Excel and have just developed a spreadsheet that adds up several columns for me. I have, it seems, applied the correct formula to work out different percentages in several columns for me. I am quite pleased with myself. I had to delete several rows from the spreadsheet but I selected "Hide" instead of "delete" on the rows that I wanted to remove! I realise now that the text has been hidden away but the total still includes the figures in that row! How can I reveal the rows that I have hi...

Row amd column heading
I have received one excel file in which rows number (1,2...) and column name (A,B,...) are in some big font 18, or maybe more. I couldn't find option to make them standard. Thanks in advance, Emilija Hi Emilija, On you toolbar you should have a font box and next to it is the size of the font. Select the row and change the fontsize to 10 for instance. Help, Answer Wizard Change font size From the topics show, select the topic that matches "Change the font or font size" --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http...