You are viewing limited content. For full access, please sign in.

Question

Question

Convert string to number in Forms

Forms
Updated March 30, 2023
asked on March 29, 2023

Hi all, 

I'm on Forms 11.2.

I'm trying to create a timesheet on the modern designer, and I'm finding it does weird things on certain calculations. My current problem is I use am trying to calculate timevalue differences, these seem to work fine in classic.

In this example I am using the calculation :  =TIMEVALUE(mon_table.mon_shift_start) for both single line and number. I get an error in the number field but it seems to show up ok in the Single Line text field. When I try to do calculations on it, it obviously doesn't work I'm assuming because its a string. 

Are there any functions to format the Single line data back to a number. And why isn't number accepting or displaying the output in the first place?

 

Thanks!

0 0

Answer

SELECTED ANSWER
replied on March 30, 2023 • Show version history

I guess it is possible there was a change between 11 Update 2 and 11 Update 3 (I'm on Version 11.0.2212.30907), but I've got TIMEVALUE working on Number fields.

For the first Number field, which is referencing the mon_shift_start variable that is not in the table, I set it to 5 Decimals, and used this calculation: 

=TIMEVALUE(mon_shift_start)

 

For the second Number field, which is referencing the mon_shift_start variable that is the table, I set it to 5 Decimals, and used this calculation: 

=TIMEVALUE(INDEX(mon_table.mon_shift_start, 1))

 

I used the INDEX formula to tell it that I wanted the value from the mon_shift_start variable on the very first row of the table.  It does not work without the INDEX formula because then you are feeding it an array of values (the entire column in the table) and TIMEVALUE does not expect an array of values.  This is of course assuming that you only ever have one row in your table.  If you are going to have more than one row in the table, then we need to reevaluate how that calculation needs to work in order to get the desired result.

1 0

Replies

replied on March 30, 2023 • Show version history

If string values are used in Numeric Calculations, you may have to use VALUE(Fieldname) when referencing the string field for the formula to properly treat the string value as a number

1 0
replied on March 30, 2023 • Show version history

Thanks for the Value suggestion! Still not having any luck but at least one step closer!

 

=SUM(VALUE(Single_Line_1),VALUE(Single_Line_2)) gives me an error

Even tried =VALUE(Single_Line_1) in the number field and it doesn't like it. 

 

0 0
replied on March 30, 2023

Can you provide a screen shot of the fields Variable names?

0 0
replied on March 30, 2023

Sure, thanks for the help!

Single line variable

Number variable

Output

Hmm one thing I have found, if I reduce the number manually by backspacing a few out of the single line it works.

But I can't seem to manipulate Single line. I've tried VALUE. It would be great if TIMEVALUE just worked on a numeric field.

0 0
SELECTED ANSWER
replied on March 30, 2023 • Show version history

I guess it is possible there was a change between 11 Update 2 and 11 Update 3 (I'm on Version 11.0.2212.30907), but I've got TIMEVALUE working on Number fields.

For the first Number field, which is referencing the mon_shift_start variable that is not in the table, I set it to 5 Decimals, and used this calculation: 

=TIMEVALUE(mon_shift_start)

 

For the second Number field, which is referencing the mon_shift_start variable that is the table, I set it to 5 Decimals, and used this calculation: 

=TIMEVALUE(INDEX(mon_table.mon_shift_start, 1))

 

I used the INDEX formula to tell it that I wanted the value from the mon_shift_start variable on the very first row of the table.  It does not work without the INDEX formula because then you are feeding it an array of values (the entire column in the table) and TIMEVALUE does not expect an array of values.  This is of course assuming that you only ever have one row in your table.  If you are going to have more than one row in the table, then we need to reevaluate how that calculation needs to work in order to get the desired result.

1 0
replied on March 30, 2023 • Show version history

Ahhh, such a newbie mistake. Thank you for taking the time and patience to help me work it out. Much appreciated!

Hmm yes it will have multiple rows, but this is a good push in the right direction.

0 0
replied on March 30, 2023

With multiple rows, I would probably put the timevalue calculation into each row of the table, then instead of refering to the INDEX(variable, 1) you can do INDEX(variable, ROW())  (at least I think that's it, I'm doing this from memory).  You can make the value hidden or something.  Then outside the table you can sum up those number values or whatever you need to do with them.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...