Convert single column values of "Table" type to a "Number" type

[Anonymous]

Hi everyone, 

I have an excel table named requestDB which has an ID column formatted as "Number" and requestID column formatted as "General" as it shows in excel.
Like this:

what I'm trying to achieve is to concatenate() RequestID and If(ID < 10, Concat(requestDB, RequestID & Text(ID)))
This is my current situation:

as you can see currently, it is a type of "Table" that's why the condition does not understand it well and this is the error message thrown at me:
then:

How can I convert that table column values? I also already tried :
Concatenate("DIR-", Text(Now(), "[$-en-US]yy"), "-", PSDepartmentValue.Text, "-" ) & If(Text(RequestDB.ID) < 10, "0" & Text(Concat(RequestDB, RequestID & ID)))

but now having 2 errors;
and

Is it possible to convert a table column value to a number type?
For now I will try to use the forAll() formula to see what can I do with that.
Hope anyone here can advise me. 

Thanks,

[Anonymous]

Hi All,

After playing with various formulas from the formula reference.

I've come up to my alternative solution, by using this formula:

Concatenate("DIR-", Text(Now(), "[$-en-US]yy"), "-", PSDepartmentValue.Text, "-" ) & If(First(Sort(RequestDB, ID, Descending)).ID < 10, "0" & Max(RequestDB, ID)+1, Max(RequestDB, ID)+1)

If you have a better solution to this please feel free to post it here as I am not a pro and want to learn more.
So please post your better practices here.

Thanks,

[iAm_ManCat · Accepted solution]

Hi,

It looks like you are trying to get a singular ID (the field created above that line), however you are trying to reference the entire column of values RequestDB.ID (which is a table).

If you change your formula to match the parameters of your formula that defines the 'new' ID, then you may be able to fix it:

RequestID: Concatenate(
                       "DIR-",
                       Text(Now(), "[$-en-US]yy"),
                       "-",
                       PSDepartmentValue.Text,
                       "-"
                       // If most recent item's ID + 1 is less than 10
                       & If (
                       First(Sort(RequestDB, ID, Descending)).ID+1 < 10,
                       "0"
                       & First(Sort(RequestDB, ID, Descending)).ID+1,
                       First(Sort(RequestDB, ID, Descending)).ID+1)
                     )

Could you give that a try and let me know if that works for you?

Cheers,

ManCat

[iAm_ManCat · Accepted solution]

Hi,

Sorry, your reply hadn't come through before I started replying - I would advise against using Max, as it's not a delegable function with SharePoint (if you are using SharePoint), so once your IDs go over 500/2000/5000 depending on what you've set in the App, then it will stop working.

Cheers,

ManCat

[Anonymous]

Hi @iAm_ManCat ,

I hope it's ok on excel, as we plan to use this as our main data source.
Any suggestions on this matter?

Thanks for your immediate reply.

Regards,

[iAm_ManCat · Accepted solution]

Hi,

Yes, I would use the same First(Sort , ... Descending).ID+1 that you used for previous steps.

When you use Max with Excel, do you get a delegation warning (blue squiggles) for it? I haven't used an Excel data source for a while, so it would be quicker for you to check. If yes, its warning, then use the formula I provided that uses First(Sort , ... Descending).ID+1, otherwise stick with Max.

Cheers,

ManCat

[Anonymous]

Hi @iAm_ManCat ,

No, I don't have any blue squiggles.

But I think I'll use what you've advised for future proofing.. haha..
Just to be sure everything would go smoothly.

Thanks for your quick response always.

Cheers,
😃

[iAm_ManCat]

You're welcome, glad to help 🙂