TM1 Forum

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

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 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...

lotsaram wrote: ↑Tue Jan 26, 2021 9:31 am 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.
And it only works within the same colum.

One would think it does not work when empty cells exist in the column:
HOWEVER there is a trick Have you ever seen this one before ? Let's create a trail......
Now that's what I call Excel trickery...

Break the chain and it stops working:
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

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.

mnasra wrote: ↑Tue Jan 26, 2021 1:28 pm 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.

Wim Gielis wrote: ↑Tue Jan 26, 2021 12:01 pm 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 ...
Which then allows the user to access properties of the data category ...
Via table lookup of power query like formulas ...
Now maybe I am a little more easily impressed than you but IMO this is quite funky.

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?

mnasra wrote: ↑Tue Jan 26, 2021 8:59 pmor can I?

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

lotsaram wrote: ↑Tue Jan 26, 2021 5:17 pmI'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:

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.