Form doesn't update a grid build on a data table from Read Range activity

Thanks @loginerror!

I will follow your updates.

Andy

1 Like

I believe this should be fixed in the latest Form 1.1.5 activity package.

Thanks @loginerror!

I will check it out at first opportunity!

Best,
Andy

1 Like

Hello @loginerror,

I gave this a shot on my end by installing the latest package. Don’t know what I’m missing, but this does not seem to work on my end. Here are the results:

Test 1: With a manually created Data Table using Build Data Table Activity (Works as before):

Test 2: Switched to a DataTable loaded from Excel (No columns rendered on Form):

I don’t know if I have to update any other package or if something has changed when it comes to binding dynamic Excel DTs.

FYI and thanks,
Andy

Hi @AndyMenon,

Can you provide that sample workflow that describes your problem.?It looks interesting, I’ll try to check what is the difference between this workflow and the one that I have that works with reading Excel as DT.

Hello @supermanPunch,

The files are attached.

Thanks!

Example2.xlsx (19.7 KB) Main.xaml (19.0 KB)

@AndyMenon I don’t know if this is a probable Solution for the problem. But this indeed seems to be a problem. As I was analysing the workflow and the difference between Excel Datatable and the Build Datatable, One Sure Difference is the type of the Columns.

If you check the Type of Columns of the Datatable generated from a Read Range, the type would be a System.Object . But where as the type of Build Datatable Columns that you had used had all System.String

So the best thing to do was change the Column Type, but I was not sure of how to dynamically change the Columns of a Datatable that already has data.

Hence I Created a New Datatable. Dynamically added the Columns from the Read Range Datatable to the new Datatable with the type as String.

Then Imported all the Rows from the Excel Datatable to the new Datatable. In this way I could get the Values to appear in the Form.

Not sure If you were expecting a different kind of answer. But this is what i got. There might be more to dig up to get an answer for the types of Columns other than String :slight_smile:

Check this Modified Workflow.

Main.xaml (14.4 KB)

These are the Changes

changeType1

1 Like

Thanks for your help with this @supermanPunch!

Yes, this is the issue. We are translating between two DataTables just to make it acceptable to the Form. All of this has to happen before the form loads. So performance is sure to take a hit in case of large worksheets.

I guess this still remains unresolved.

-Best,
Andy

1 Like

Hi @AndyMenon

I checked the attached workflow. Normal datatable works for me. But excel didnt. I seems like there is an issue with Column Name/type.

DataTable column name should be string and only Alpha Numeric. Please make sure that you are following the above rule.

Thanks, I will keep your suggestion in mind. That said, I don’t create these Excel documents. They are created by business groups for whom we need to automate processes. Asking these groups to simply change their Excel format may not be a possibility all the time as these documents may be inputs to, or outputs from other processes.

For example, I receive an Excel downloaded from a CRM application. Using this input file, I have to generate an output Excel that has to be in a certain format to be accepted by a Cloud SaaS application. In both cases, we don’t have a say in changing the formats of the reports we work with.

We have to factor in these real world conditions. But I do agree that if we are building something from scratch we will have some say in how these input/output structures need to be defined.

-Andy

1 Like

I can understand. But again Forms dont treat how you create a datatable. as long as datatable has alpha numeric column names , Forms should work.

These are the format which Forms Expect.

Can the FormFieldsInputData property be used instead of formfieldscollection for updating the form field values dynamically?

Quick question: Do you consider blank spaces Alpha-numeric? This is my test Excel posted in the forum upstream.

image

@AndyMenon

We looked into it a bit more. Basically, the culprit here is the Read Range activity, which loads the DataTable with columns of type Object, which in turn is not supported.

Therefore, the solution here is to process the loaded data table variable to another one that has primitive column types.

This way you will be able to dynamically load as many columns as you want.

EDIT
Another potential workaround I found is to convert the source file to a CSV file. When I loaded a sample file using the Read CSV activity, it worked properly.

2 Likes

Thanks @loginerror.

This is a possible workaround and in my opinion, might be a better alternative that can be controlled external to the Robot as opposed to the option of adding code inside the Robot to convert the source DataTable from “Object” to “primitive”.

Personally, I would avoid this conversion unless absolutely necessary because the Robot has to have knowledge of the mappings between the incoming Object datatype and its primitive counterpart.

1 Like

I suppose, given the input is excel data, you could quite safely map all data table columns with objects to another data table with strings.

Yep! That is how I build DataTables instead of using the Build DataTable Activity. I have a generic sequence that contains VB code that accepts a string of column names as input and returns a datatable as output. All input columns are considered as String. I maintain the list of CSV column names in the config.xlsx file.

This keeps changes to my Robot code minimal.
:slight_smile:

2 Likes

Hi,

I have 2 questions about this. I am using Create Form Task, and send the tasks to orchestrator, the project is about sending the tasks (100), and then get all the tasks from orchestrator and see the human changes one by one. What activity can i use to recover all the orchestrator tasks and see the contents of each one individually?

Other question, and I leave my topic here (How to Edit Inserted Table in Action Center - #4 by paras.gera), I insert a table into task form, but my problem is that i cannot edit the table contents in actions, the table fields are blocked. Can you help?

convert json to datatable

Small update - the team has looked into the issue and a fix should be coming in one of the future versions of the package :slight_smile:

1 Like