Need help with field mapping

[michalikm]

Hello!

I have an input data tool that is modeled off a report where the data starts on row 9. When the user browses to this file via file browse, it's possible there will be additional/unnecessary columns of data, so I'd like to utilize field mapping if possible to only grab what is necessary for the workflow to run. How can I get field mapping to work when my data won't start on the first row?

Thank you in advance for your help!

[jrlindem]

My understanding of Field Mapping (in the Interface Designer) is how you allow your user to correspond their fields to the fields from your workflow.  This wouldn't necessarily help you remove out the extra rows ahead of the data/headers themselves.

If you're building an app, though, could you require your user to remove out the rows above the header before selecting it?

Alternatively, can you create some rule(s) to use in a Filter tool to identify the correct rows and only retain them?  This isn't ideal though if you need your user to map field names as a part of the input process.

Not a great solution, but some suggestions in the hopes of getting you started, -Jay

[michalikm]

Thank you for your response, @jrlindem! So the users would input an xlsx file where the first few rows contain some live formulas, tables, and summary information, while all the "data" starts on row 9. It's possible that we can ask them to remove the unneeded rows, but not ideal. The file by default contains a minimum of 15 - 20 columns of data, but only 5 columns are being utilized in the workflow. I just found it strange that when using the option on the input data tool to "Start Data Import on Line" function to start the upload at the correct row, a file browse will appropriately identify the headers and data, but when the field mapping option is selected on the file browse, it can't. 

I would prefer to not ask users to delete the header information. Is there any other way I can accomplish allowing the user to align their file to the correct fields within the interface?

[jrlindem]

Yes, I understand what you're saying.

If you're okay with the workflow auto-detecting fields, then I would prompt the user to specify what row the data import starts on:

You can prompt the user to pick the import line and whether or not that is a header row.  Then you can simply allow the workflow to auto-detect based on field name.  

Would that be an option?  -Jay

[dreldrel]

If the data always starts at row 9 and within a fixed column range, you can hard-code this in your Input tool. In the Input tool configuration, you’ll find an option called “Start Data Import on Line” where you can specify the starting row.

[michalikm]

Unfortunately no, autodetection won't work since it's likely that header names will not be consistent from report to report, and if the app assigns one of the 5 needed fields incorrectly, the user may not catch it and the output would be compromised. No matter what the solution would be, the user needs to be able to confirm that field names are being assigned correctly via the interface somehow. Since the header row will always be in the same location, I assumed this would be possible through the input data tool options and the file browse field mapping... I just can't for the life of me figure out why it doesn't work this way lol.

@dreldrel Correct, that options works normally, but when turning on field mapping within the file browse options, it wants you to map fields based on the first row of data, not the row you've asked the input data tool to start reading from.

[CoG]

You could also set this up as a chained app. The first takes in and preps the selected file, then opens a second app with field mapping interface.

[michalikm]

That is an interesting thought @CoG... While I disdain working on chained apps lol, if that allows me to accomplish what I'm working on, I'll take it. How would I go about setting something like that up?