cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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:


requestDB.png

 

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

requestDB.png

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:
requestDB1.pngthen:

requestDB2.png

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;
requestDB3.pngand

requestDB4.png

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,

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User II
Super User II

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

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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,

Super User II
Super User II

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

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

Anonymous
Not applicable

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,

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

Anonymous
Not applicable

Hi @iAm_ManCat ,

No, I don't have any blue squiggles.
no-blue-squiggles.png
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,
😃

You're welcome, glad to help 🙂


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (71,276)