Validating user input for on-demand data

[Sundaram Sridharan]

I have a couple of input boxes that are tied to two data on-demand tables (through SQL queries). I have these inputboxes converted to listboxes and then these listbox properties are linked to those tables. I also have an action control to trigger the on-demand data load (which are kept as manual download). Everything is working fine. How do I now verify whether the user entered anything in the input boxes before I execuete the reloadData command in that script. If the input is empty then it is going to bring in everything from database which will bea disaster.

[Alain Martens]

Hi SidSri,

I'd suggest using an approacht with an in memory table an on demand table and a document property.

The in memory table and the on demand table both point to the same data source. However the in memory table will function as a helper table to provide you the unique values in a column (which the user can choose from in your listbox). The user selection will be stored in a document property which will be used to control the on demand table.

This will give you an input selction box where a user can select a unique value from the column. Then when the user select a value, the on demand table is reloaded automatically.

Here are the steps;

1. Setup In Memory Table.

The in memory table can be constucted with a custom query and the 'distinct' keyword to prevent this table from becoming big. Next to that it's a good idea to bring over the only column that you'll need for the user selection.

2. Setup a document property

Create a documnent property which will serve as a placeholder for the users selection. Choose to set the property value through unique values in column.

3. Setup On Demand Table.

Add the on demand table and specify for 'define input' that you'd like to use a value from (field/properties/expression). Then select the document property that you've setup in step 2.

Please see screenshots for more info. Hopefully that all of this makes sense.

Best regards,

Alain Martens

[Shandilya Peddi]

One other thing you can do is in your relodScript, check the document property value as a validation step if it is empty then do nothing else load the data table

You can access the document property using

value= Document.Properties["myPropertyName"]

[SAMUT Fabrice]

My question is about the data on demand.

About your answer on a previous post, you wrote: "check the document property value as a validation step if it is empty then do nothing else load the data table"

This is exactly what I want to do but could you please write me the code to do this.

When the document property is empty, load the table.

Thank you ​

​

​

​

​

[Alain Martens]

I think you use IronPython correct? In that case you could test the first if 'value' is empty or not. If not, only refresh the table (or tables) then.

Something like this:

# read your user selection doc property.value= Document.Properties["myPropertyName"]# test if user has selected something. if value != "": # Reload all data in all tables Document.Data.Tables.ReloadAllData()

More info on reloading data tables (or specific ones) can be found here:

https://community.spotfire.com/s/article/How-to-Refresh-or-Reload-Data-using-IronPython-script-in-TIBCO-Spotfire

Best regards,

Alain

[SAMUT Fabrice]

Thank you for your answer but I still strugging with this piec eof code

Here is y initial ironpytho sript to reload my tables:

def CreateTables():

Tbls = List[DataTable]()

Tbls.Add(Document.Data.Tables['A'])

Tbls.Add(Document.Data.Tables['B'])

Tbls.Add(Document.Data.Tables['C'])

Tbls.Add(Document.Data.Tables['D'])

for i in Tbls:

Document.Data.Tables.Refresh()

==> it works fine

Now I add a document property (named phase) on the input of the data on demand for the table B and I want to load all this table if the value of this document property is empty.

If I add your piece of code, will it be like this ?:

def CreateTables():

Tbls = List[DataTable]()

Tbls.Add(Document.Data.Tables['A])

Tbls.Add(Document.Data.Tables['C'])

Tbls.Add(Document.Data.Tables['D'])

for i in Tbls:

Document.Data.Tables.Refresh()

# read your user selection doc property.

value= Document.Properties["phase"]

# test if user has selected something. 

if value != "":

# Reload the table B

Document.Data.Tables['B'].ReFreshOnDemandData()

I tested it and it doesn't work

[Alain Martens]

Hi,

Can you be a bit more specific about what doesn't work? Do you get an error? If so, which one?

Please note that indentation is important in IronPython. Based on you code snippet it could be that you need to indent (a tab in front of) Document.Data.Tables['B'].ReFreshOnDemandData().

Hope this helps.

Best regards,

Alain

[SAMUT Fabrice]

Hi Alain,

Thank you for your answer.

I resumed my issue on a word document.

Thank you for your help

[Alain Martens]

Hi Samut,

Thanks for the document. So I guess this is a different use case than the original post correct? The way I understand it now it that you have an on-demand table which you like to load based on the users input stored in the document property: 'phase_doc'.

However when the user does not supply a value all data should be loaded.

Is this a faire representation of the use case you're trying to solve?

Thanks,

Alain

[SAMUT Fabrice]

Hi Alain,

Thank you for your answer.

In fact, this is a little bit different than the original post.

And yes, this is exactly the representation of my ​issue.

Do you think there's a solution for this problem?

​

Thank you ​

[Alain Martens]

Hi Samut,

Ok got it. In that case I think I would use a different approach all together.

I would use a in-memory table (instead of an on-demand table). This will be similar to when the users doesn't provide any input.

And then I would create a property control (like a dropdown or input field depending on your UX requirements) and use that property control as a filter for the in-memory table such that the table is filtered to only those fields that are relevant for the user.

Does this help?

Thanks,

Alain

[SAMUT Fabrice]

Thank you for your answer.

I was also thinking about using an in-memory table but in my use case, it will be very huge because one of my column can have more than 200 ​different values and the other columns(10) about 50 different values.

The in memory table will have more than 10 million rows and it will take a lot of time to load while opening the report.​

Is it the only way to do?

Thank you ​

[Alain Martens]

Hi Samut,

10 million rows isn’t that bad right? But if that’s taking too long then the on-demand option will not be feasible either correct?

Say you are able to configure it to load everything when the user doesn’t specify an input. Loading the data will then take equally long as loading all data upfront with an in memory table.

alternatively you could look into scheduled updates which will load data in memory for web users and the user can start right away with the analysis once it’s in memory.

hope this helps.

Alain

[SAMUT Fabrice]

Hello Alain

I tried your method and it seems to work fine, thank you for that.

But there is still an issue

.I explain :

on my in-memory table, I have specific character like: PASSENGER'S DOOR ' and the data on demand failed on those specific characters.

When I remove the rows with "PASSENGER'S DOOR", the data on demand works fine.

Do you know how I can do to pass all the characters including the specific ones?

Thank you for your help

[Alain Martens]

Hi Samut,

Glad to hear.

Wrt your follow up question, I think it's easiest to replace this value with a string that does work fine (right click on the value in a table visual and choose 'replace value').

Then when you reload the data table or dashboard it should work fine.

Thanks,

Alain

[SAMUT Fabrice]

Hi Alain,

I try to do it but I think I don't have the proper release of spotfire to do that.

Anyway, I have another question : how I can convert hexadecimal value to decimal value?

Is there a function hex2dec like in excel in Spotfire?

I want to convert this value : 15C(h) in decimal.

Thank you

[Alain Martens]

Hi Samut,

I can check if that functionality is available for you. What version of Spotfire are you on?

Wrt your other questions, there are various ways to convert your hexadecimal value to a numeric one.

You could use a TERR Expression on an axis of a specific visual, you could create a calculated column or you can use a data function in R or Python for example (see attached screenshot).

I've created a super simple R data function that shows how this is done. If you want we could have a short call where I can walk you through how to set this up.

Let me know if you want that. Perhaps I could also address any other questions you may have.

Thx,

Alain

 

[SAMUT Fabrice]

Hello Alain,

I have the 11.4.3.59 build version of spotfire.

I will be glad to share a phone call with you on your R data function.

Could you tell me when you will be available?

Thank you

[Alain Martens]

Hi Samut,

The 'replace values' feature is part of the 11.4 version you're using. Might be best indeed to have a call to go through this and also show you the R Data function implementation. Perhaps easiest to align through email?

I do have time now for a call. Not sure if you are too. I'm in CET timezone. My email is: amartens@tibco.com

Thanks,

Alain