Key

[TonyHollywood]

Hi Community

I want to create a unique key that I can then join to another table with multiple rows. The purpose being that I can then recover the rows in the second table based on the key in the first table. I tried using the DateTime.FixedLocalNow() as part of the key but after joining to the second table the time is slightly different from the time in the created key and does not match. Any suggestions as to how to create the key.

[m_dekorte]

Hi @TonyHollywood,

Create an separate query to hold the DateTime.FixedLocalNow() value

You can refer to that query by name when generating keys in both tables

I hope this is helpful

[TonyHollywood]

• Hi m_dekorte thank you for your speedy reply. I have tried this but, unless I am doing something wrong  each time I reference the separate query it comes back with an updated time. Any suggestions?

[m_dekorte]

Hi @TonyHollywood , 

You are right. Don't know why you want to include time in your key but maybe consider:

1. Lowering the granularity, how about stripping off seconds?

DateTime.ToText( DateTime.FixedLocalNow(), [Format= "dd-MM-yyyy hh:mm"])

2. Round time for exampl up to quarters:

DateTime.From( Number.RoundUp( Number.From(DateTime.FixedLocalNow()) * ((24*60)/15), 0)/ ((24*60)/15))

3. Or setting an arbitrary time value

I hope this is helpful

[TonyHollywood]

Thanks again m_dekorte, really appreciate your help. At least I know now I am not going mad. 

I am using the datetime as I want to generate a key that is unlikely to be replicated.  Any suggestions as to how I can generate something unique out a function that will remain 'static' ?

[m_dekorte · Accepted solution]

I think you might be are out of luck there, M functions "update" when evaluated. If you need a "static" value that will have to be provided by a datasource. For example, the date/time a file or an export was created and so on. 

Depending on your scenario and its complexity, you could generate a list or column of values  (indices) from which you take the first "free" number...

[TonyHollywood]

Many thanks for your help m_dekorte.