cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rpafurong17
Level: Powered On

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
Super User

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

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

Super User
Super User

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

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

Super User
Super User

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

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

7 REPLIES 7
rpafurong17
Level: Powered On

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

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
Super User

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

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

Super User
Super User

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

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

rpafurong17
Level: Powered On

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

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,

Super User
Super User

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

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

rpafurong17
Level: Powered On

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

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,
😃

Super User
Super User

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

You're welcome, glad to help 🙂




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 429 members 5,283 guests
Please welcome our newest community members: