cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Alphanumeric value not updating in SQL.

Hello Community,

 

I am trying to build an App which updates one alphanumeric value and integer value in SQL. 

When i am trying to update integer value is works fine, but the alphanumeric value is not updating. When i give integer like '2368' in alphanumeric value it gets updated, but when i give alphanumeric value like 'DR0977'  the value is storing as empty in SQL record.

 

cv2qm_0-1596476751926.png

 

 

Patch(
'[dbo].[Table]',
{
STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
AWARD: Value(TextInput1_3.Text),
AMOUNT: Value(TextInput1_2.Text)
}

 

Any suggestions. 

Thanks !

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Patch(
    '[dbo].[FINAid_PowerApps_Dev]',
    {
        STUDENT_SYSTEM_ID: ComboBox1.Selected.STUDENT_SYSTEM_ID,
        AWARD: Text(TextInput1_3.Text),
        OFFER_AMOUNT: Value(TextInput1_2.Text)
    }
)

I resolved it with small fix.

I used Text for Alphanumeric value. 

View solution in original post

3 REPLIES 3
Highlighted
Super User
Super User

Hi @cv2qm, I think the issue is that you are using the Value function on what you are trying to write to SQL. That function makes a text value into a numerical value, so any non-numerical values would return nothing (essentially an error state). If you try changing your Patch code to the following, it should work:

Patch(
    '[dbo].[Table]',
    {
        STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
        AWARD: TextInput1_3.Text,
        AMOUNT: Value(TextInput1_2.Text)
    }
) 

 

If that doesn't sort things out, feel free to @ me. I'm more than happy to follow up!

Highlighted
Power Apps
Power Apps

Hi @cv2qm ,

It sounds like you want to write a value back to your SQL table and something is not working right for you. I pasted your formula here using the </> button in the toolbar:

 

 

Patch(
    '[dbo].[Table]',
    {
        STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
        AWARD: Value(TextInput1_3.Text),
        AMOUNT: Value(TextInput1_2.Text)
    }
) 

 

 

 

The patch function attempts to convert whatever is in the label into a number as best as it can. In the case of 2368, the conversion is easy because it's already a number. In the case of DR0977, the number is not picked out as well. 

 

You can test this out. Insert a label and set its text as below to see what would return:

 

 

Value("DR0977")

 

 

 

Instead, it may be most accurate to retrieve the digits by using the Match() function. It uses regex which is very powerful, and there are some out of the box patterns you can use without need to memorize any regex. Here's an example that would retrieve the digits:

 

 

 

Value(Match("DR0977",Match.MultipleDigits).FullMatch)

 

 

This means, "Look for a pattern of multiple digits in the string and return a record containing the full match, sub matches, and an integer representing where the match was found. But from that record, only return the fully matched string. Then convert that match, which is a string, to a value instead."

 

In context, this is how it might look:

 

 

Patch(
    '[dbo].[Table]',
    {
        STUDENT_ID: ComboBox1.Selected.STUDENT_ID,
        AWARD: Value(Match(TextInput1_3.Text,Match.MultipleDigits).FullMatch),
        AMOUNT: Value(TextInput1_2.Text)
    }
) 

 

 

 

Let me know if this helps. 👍

Highlighted
Frequent Visitor

Patch(
    '[dbo].[FINAid_PowerApps_Dev]',
    {
        STUDENT_SYSTEM_ID: ComboBox1.Selected.STUDENT_SYSTEM_ID,
        AWARD: Text(TextInput1_3.Text),
        OFFER_AMOUNT: Value(TextInput1_2.Text)
    }
)

I resolved it with small fix.

I used Text for Alphanumeric value. 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,908)