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' and 'Thumbs Up' those who deserve it!

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' and 'Thumbs Up' those who deserve it!

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' and 'Thumbs Up' those who deserve it!

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' and 'Thumbs Up' those who deserve it!

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' and 'Thumbs Up' those who deserve it!

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' and 'Thumbs Up' those who deserve it!

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 Smiley Happy




Don't forget to 'Mark as Solution' and 'Thumbs Up' those who deserve it!

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 291 members 4,452 guests
Please welcome our newest community members: