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

Am I misreading the Forms documentation? Or, does the Dynamic form not work with DataTables generated by reading an Excel?

If I create a DataTable using the “Build Data Table” activity and fill it with some test data, it works.

But if I load basic string data from an Excel Read Range into a runtime Data Table, it does not render on the form at run time!

Is this by design? Or am I missing something?

Success/Fail Screen shots below:

A Static Data Table

:slight_smile: Success!

Loaded this Simple data using Excel App Scope >> Read Range Activity

I can see the Excel Data loaded in the Data Table

:astonished: What?! Why?

You can do this like this:

  • pass your excel data to DataTable

  • serialize it to json:
    image

  • add to your form dynamic grid
    image

  • add your property name to grid
    image

  • pass the json to your grid using this attribute

  • you need also add same number of text fields as you have columns to datagrid and use same property name for each as your column name in excel sheet


    etc.

And it should work.

My Example:
Excel:
image

Form:

EDIT:
The way you would like to use is working as well but need additional step.
So let’s assume I have the same excel:
image
(need to remember to use add header attribute)

  • I will use Read Range as same as you and assign output of it to pass data to Form:
    image

  • then I will open Form Designer and I need to add Data Grid and put into it as much text field as I have columns in excel.

  • finaly I need to edit each text field and write in propertyName of each one the name of column
    image
    And it should work as well

1 Like

Hi @Pablito! I will work on this solution!

My impression was that one of the reasons why we have an in-memory Data Table is to create a source-agnostic data structure.

When I put my project through the debugger, I could not see any visible difference between the Data Table loaded from the Excel vs. the one that was created using the “Build Data Table” activity.

In other words, the Dynamic forms binding mechanism should treat all Data Tables the same when binding regardless of how that DataTable was created and render the form.

A few questions:

I see that we are defining static column mappings here. So does this make it static binding then? If the Excel is too wide, then the Form design becomes labor intensive. I have Excel sheets that run almost up to column “AJ”!

What happens if the business owners decide to change the format/design of the underlying Excel sheet? (I’m in the middle of an Attended Automation design and one of our Excel formats changed midway between two meetings!) :astonished:

We are going through two levels of translation - Excel to Data Table, and then Data Table to JSON - how will this solution work in terms of high-volume performance (Example: 65000 records with 40 columns say?)

Thanks for posting the solution with such clarity!
Andy

1 Like

Hi @AndyMenon

Forms don’t consider how you create datatable. If you have datatable when you execute forms, it will render it.
Forms Datatable can be dynamic, Meaning you dont need to give column names during design time. But when you execute the workflow, while rendering the form we create runtime (dynamic) schema then we will show the form.
you can utilize realtime forms (DO Block) to change the data inside grid. But not the structure (I mean, column is fixed once it renders)

Hope you are clear. Any more questions?

1 Like

Hi @AndyMenon

It would seem like this is a bug and will be fixed. I moved the discussion to a separate topic to easier track it.

We’ll update here when it is fixed :slight_smile:

You just explained back to me what my question was to begin with.

And I don’t know what you mean by asking me if I have any more questions.

Isn’t that why forums are for?

Thanks

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

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.

1 Like