Tm1web string data entry autofill

[mnasra]

hello, is there a way to make tm1web behave like Excel when entering string data? I need to enter cities, but I would like tm1 to suggest the city based on the first letters I type. if not, add this new city to the ‘picklist’.
thanks

[lotsaram]

It's been said many times already but TM1 WEB IS NOT EXCEL! It is the contents of an Excel spreadsheet rendered (hopefully faithfully) via a javascript engine. It does support Picklists and also native Excel validation lists. However the newer, funkier Excel features where inbuilt AI says "hmm it looks like this field represents the name of a city, let me suggest a list of cities for you" simply does not exist.

[MarenC]

I didn't even know this could be done in Excel never mind TM1!

I tried typing Lon into my excel and it didn't autofill for London...

[Wim Gielis]

It's been said many times already but TM1 WEB IS NOT EXCEL! It is the contents of an Excel spreadsheet rendered (hopefully faithfully) via a javascript engine. It does support Picklists and also native Excel validation lists. However the newer, funkier Excel features where inbuilt AI says "hmm it looks like this field represents the name of a city, let me suggest a list of cities for you" simply does not exist.

I agree that it does not exist but hey, we cannot call it "the newer, funkier Excel features where inbuilt AI says [...]" It exists for ages !

The way I understand the requirement is that a column (in Excel) contains cells with text contents. If, in the absence of empty cells, someone types the start of an existing entry, Excel will complete/suggest the rest of the contents. Only when the first part of the cell contents is unique.

There is no AI type of wizardry in Excel.

03.png (4.57 KiB) Viewed 4719 times

And it only works within the same colum.

04.png (5.58 KiB) Viewed 4719 times

[Wim Gielis]

One would think it does not work when empty cells exist in the column:

05.png (6.48 KiB) Viewed 4715 times

HOWEVER there is a trick Have you ever seen this one before ? Let's create a trail......

06.png (8.67 KiB) Viewed 4715 times

Now that's what I call Excel trickery...

Break the chain and it stops working:

07.png (8.23 KiB) Viewed 4715 times

You can create the chain in a hidden column. But better yet, AVOID EMPTY CELLS AT ALL COST !

If you don't like the x any character is fine

[Wim Gielis]

03.png (11.08 KiB) Viewed 4706 times

[mnasra]

Thank you all, yes, the requirement was that I wanted to be able to enter a new item in a picklist if mine was not already there. the city was an example.
but thank you Wim for the excel tests. Funny, I never really knew why sometimes it did not work in excel.

[tomok]

Thank you all, yes, the requirement was that I wanted to be able to enter a new item in a picklist if mine was not already there. the city was an example.

If it is a traditional TM1 picklist then I don't see why you can't have a cell on the form/report where you can enter the new picklist value and then have an action button that runs a TI process that updates the picklist. The code in the TI process is obviously going to dependent on what type of picklist it is: static, dimension, subset, or cube value, but I don't see any reason why you wouldn't be able to make it work.

[lotsaram]

I agree that it does not exist but hey, we cannot call it "the newer, funkier Excel features where inbuilt AI says [...]" It exists for ages !

I'm not talking about autofill (which exists yes since forever). Or even about flash fill (which is a little new and funky, and even, dare I say it, a bit A.I.ish). But about the new "data types" feature (which does work pretty well for things like geography). Using the geography data type entries can be auto-completed for items which don't already exist in the list above (and aren't predefined in a data validation), but it can also do other more funky stuff like below.

E.g. Excel recognizes a list might be geographic in nature ...

2021-01-26_17-42-14.png (18.95 KiB) Viewed 4667 times

Which then allows the user to access properties of the data category ...

2021-01-26_17-44-55.png (45.67 KiB) Viewed 4667 times

Via table lookup of power query like formulas ...

2021-01-26_17-46-52.png (17.96 KiB) Viewed 4667 times

Now maybe I am a little more easily impressed than you but IMO this is quite funky.

[mnasra]

Hey, i am impressed with these new features in excel. nice to have, and AI like.

But you are right Tomok, if I use a conventional picklist
I know how to do it with 2 different fileds, but I was hoping I can enter a new value on the field that is defined as picklist. or can I?

[Wim Gielis]

or can I?

Without VBA (and it does run on TM1 Web), no.

[Wim Gielis]

I'm not talking about autofill (which exists yes since forever). Or even about flash fill (which is a little new and funky, and even, dare I say it, a bit A.I.ish). But about the new "data types" feature (which does work pretty well for things like geography). Using the geography data type entries can be auto-completed for items which don't already exist in the list above (and aren't predefined in a data validation), but it can also do other more funky stuff like below.

Your interpretation of the question in this topic has been the right one instead of my interpretation.
I know about FlashFill and the new data types: geography, stocks, and many more that are coming towards Excel (chemistry, movies, ... and also your own data types like Customer or Product). I gave a presentation last December about Excel, what it currently is and what the near future will bring.

For example, lookup of a Stock data type Ticker symbol (of Microsoft) to retrieve its number of employees:

03.png (10.61 KiB) Viewed 4643 times

[MarenC]

I am still not seeing how the Geography option autofills the next cell with a suggested city, when you type in a few letters.

It doesn't seem to be working for me.