cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markslosberg
Resolver I
Resolver I

"Value must be between 0 and 100" error occurring occasionally on the execution of a Patch statement

I have a Patch statement has been working reliably.  It is built from the results of two separate Flows that extract info (first SoldTo and then ShipTo records) from SQL Stored Procedures into Global and Context Variables and screen controls which I then use to allow the user to edit the values and then place the edited info the CDS Sales Order Header entity.  

 

As I said, it had been working reliably with a subset of the SoldTo and ShipTo records that were being retrieved.  When I expanded my testing to include a broader array of SoldTos and ShipTos selected randomly but with a "broader brush", I have found that on some of the selected values, when I go to Patch the record (and this only happens on the creation of a new Sales Order using the Defaults function and not Edits to existing records), I get a Patch failure with the error "Value must be between 0 and 100".  I have reviewed my entity structure and that simply doesn't ring a bell and I don't have any values that fall into that range and very few overall restrictions other than a couple of required fields. I have never seen this error code before.

 

I have been "detectiving" the SoldTo/ShipTo combos that work vs. those that fail and have not seen any visible differences between the two types other than some work perfectly and others throw the error.

 

Has anyone else encountered this error message and have some understanding of it such that I can continue to troubleshoot what the difference between a Patch that works and one that doesn't.

 

Here are a couple of screen prints. The first shows the error code in the statement where I had a Patch failure.  This error message moves around so it doesn't seem to be pointing at any particular offending part of the statement.Value must be between 0 and 100 error.png.  And here is the same code after a successful Patch with no errors thrown.  I have tracked each of the data elements and don't see anything obviously different between the ones that work and the ones that don't.

 

Without Value must be between 0 and 100 error.png

Here is the entire statement under the "Save" button.  The data elements are being extracted from the galleries associated with the colSoldToCustList and colShipToList that are populate the two galleries where I am pulling data from the selected record(s).

 

 

 

 

If(
    SOHdrEditMode,
    UpdateContext(
        {
            selectedOrderCTX: Patch(
                'Sales Order Headers',
                LookUp(
                    'Sales Order Headers',
                    cr66a_salesordernumber = selectedOrderCTX.cr66a_salesordernumber
                ),
                {
                    cr66a_soldtoname: selectedOrderCTX.'SoldTo Name',
                    cr66a_soldtocontactname: txtSOHdrContactName.Text,
                    cr66a_soldtocontactmobilephone: txtSOHdrContactMobile.Text,
                    cr66a_soldtocontactemail: txtSOHdrContactEmail.Text,
                    cr66a_soldtoaddressid: selectedOrderCTX.SoldToAddressID,
                    cr66a_shiptoname: selectedOrderCTX.'ShipTo Name',
                    cr66a_shiptocontactname: txtSOHdrShipToContactName.Text,
                    cr66a_shiptocontactmobilephone: txtSOHdrShipToMobile.Text,
                    cr66a_shiptocontactemail: txtSOHdrShipToEmail.Text,
                    cr66a_shiptoaddressid: selectedOrderCTX.'ShipTo AddressID',
                    cr66a_seedorderpreference: drpSeedOrderPreference.Selected.Value,
                    cr66a_salespersoncommissioncode: selectedOrderCTX.cr66a_salespersoncommissioncode,
                    cr66a_salesorderdate: dteSalesOrderDate.SelectedDate,
                    cr66a_requiredarrivaldatetime: dteRequiredByDate.SelectedDate + Time(
                        Value(drpHeaderRequiredHour.Selected.Value),
                        Value(drpHeaderRequiredMinute.Selected.Value),
                        0
                    ),
                    cr66a_ordertype: drpOrderType.Selected.JDECode,
                    cr66a_generalshipmentrequirements: txtSOHdrShippingPreferences.Text,
                    cr66a_customerpo: txtSOHdrCustomerPO.Text,
                    cr66a_soldtonumber: Value(lblSoldToCustomerNumber.Text),
                    cr66a_shiptonumber: Value(lblShipToCustomerNumber.Text),
                    cr66a_shiptolongaddressid: selectedOrderCTX.'ShipTo LongAddressID',
                    cr66a_pendingorderstatus: drpPendingOrderStatus.Selected.Value
                }
            )
        }
    );
    Set(
        changedOrderLineData,
        false
    ),
    UpdateContext(
        {
            selectedOrderCTX: Patch(
                'Sales Order Headers',
                Defaults('Sales Order Headers'),
                {
                    cr66a_soldtoname: selectedOrderCTX.'SoldTo Name',
                    cr66a_soldtocontactname: txtSOHdrContactName.Text,
                    cr66a_soldtocontactmobilephone: txtSOHdrContactMobile.Text,
                    cr66a_soldtocontactemail: txtSOHdrContactEmail.Text,
                    cr66a_soldtoaddressid: Text(selectedOrderCTX.SoldToAddressID),
                    cr66a_shiptoname: selectedOrderCTX.'ShipTo Name',
                    cr66a_shiptocontactname: txtSOHdrShipToContactName.Text,
                    cr66a_shiptocontactmobilephone: txtSOHdrShipToMobile.Text,
                    cr66a_shiptocontactemail: txtSOHdrShipToEmail.Text,
                    cr66a_shiptoaddressid: selectedOrderCTX.'ShipTo AddressID',
                    cr66a_seedorderpreference: drpSeedOrderPreference.Selected.Value,
                    cr66a_salespersoncommissioncode: selectedOrderCTX.cr66a_salespersoncommissioncode,
                    cr66a_salesorderdate: dteSalesOrderDate.SelectedDate,
                    cr66a_requiredarrivaldatetime: dteRequiredByDate.SelectedDate + Time(
                        Value(drpHeaderRequiredHour.Selected.Value),
                        Value(drpHeaderRequiredMinute.Selected.Value),
                        0
                    ),
                    cr66a_ordertype: drpOrderType.Selected.JDECode,
                    cr66a_generalshipmentrequirements: txtSOHdrShippingPreferences.Text,
                    cr66a_customerpo: txtSOHdrCustomerPO.Text,
                    cr66a_soldtonumber: Value(lblSoldToCustomerNumber.Text),
                    cr66a_shiptonumber: Value(lblShipToCustomerNumber.Text),
                    cr66a_shiptolongaddressid: selectedOrderCTX.cr66a_shiptolongaddressid,
                    cr66a_pendingorderstatus: drpPendingOrderStatus.Selected.Value
                }
            )
        }
    );
    Set(
        changedOrderLineData,
        false
    )
);
Set(
    showCustList,
    false
);
Set(
    showShipToList,
    false
);
Set(
    changedOrderHdrData,
    false
);
Set(
    showSavedHeader,
    true
);
Set(
    _SOHeaderDisplayMode,
    true
);
Clear(colSoldToCustList);
Clear(colShipToList)

 

 

 

Thanks,

Mark

1 ACCEPTED SOLUTION

Accepted Solutions

That wasn't exactly the solution but it got me pointed in the right direction.  It wasn't the DropDownBox that was too long but one of the other text strings that came in from the SQL Stored Procedure call.  As it turned out the source data was not well structured and consistent (which is what the core symptom was (some records were just fine, others not).

 

I never figured out exactly which string was too long (I have a hunch) but I just went through and did a Left truncate at 100 characters on every field and that solved it.  And my app is that much more error-protected now.

 

It might be nice if the error message was just a bit more descriptive and told me which of the 21 fields was causing the error instead of the generic "Value must be between 0 and 100" which could have been any of them.  Then it does suggest that it was indeed a value that was causing the problem rather than a string length violation.

 

Thanks for the tip. I needed it;-).

 

Mark

View solution in original post

8 REPLIES 8
EricRegnier
Super User II
Super User II

Hi @markslosberg,

Have you tried increasing the max length of your field? In make.powerapps.com, expand Data then select Entities. Search for your entity and open it. Under the Fields tab, select your field and a right pane will open. Under Advanced Options, you can increase the Max Length. Don't forget to save and publish!

Here's more info about editing a field: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-edit-field-portal#edit-a... 

Hope this helps!

Thanks, I will try to look at that but I don’t think that is my issue. I don’t have anything that is currently constrained to between 0 and 100 and the challenge is the error message doesn’t give me any guidance as to which field or value is responsible for the message. As I said, most of the SoldTo/ShipTo combos work just fine and from what I can see, the info being fed to the Patch command is of identical type and length for both the ones that work and the ones that don’t. Obviously they aren’t actually identical but diagnosing the difference/issue has been a challenge. 

This seems to be something a bit more arcane. A little more info in the error message would be useful.

I'd look at drpSeedOrderPreference.Selected.Value and drpPendingOrderStatus.Selected.Value.... anything that's a .Value field rather than a .Text and then check out the max/min in the CDS entity

That wasn't exactly the solution but it got me pointed in the right direction.  It wasn't the DropDownBox that was too long but one of the other text strings that came in from the SQL Stored Procedure call.  As it turned out the source data was not well structured and consistent (which is what the core symptom was (some records were just fine, others not).

 

I never figured out exactly which string was too long (I have a hunch) but I just went through and did a Left truncate at 100 characters on every field and that solved it.  And my app is that much more error-protected now.

 

It might be nice if the error message was just a bit more descriptive and told me which of the 21 fields was causing the error instead of the generic "Value must be between 0 and 100" which could have been any of them.  Then it does suggest that it was indeed a value that was causing the problem rather than a string length violation.

 

Thanks for the tip. I needed it;-).

 

Mark

View solution in original post

Agreed, the error messaging could be more informative, especially on long functions - glad you got it worked out!

Thank you.  You were right on but it didn't click with me until later.  The problem was the error message.  Based on that, I was hunting for a "value" in the numeric sense rather than a string length.  A clearer error message would have made this a lot quicker.

 

I also had to really inspect the data that was coming in from my data source which was not consistent.  All in all, while this was a bit of a 6-8 hour detour, my app is much more hardened now (across all of the different fields, numeric and string) and I have a design pattern to reuse for the future ensuring that my Patch statements closely match my CDS schema constraints.

Had to say that left Truncate is 'not' the best way to solve this. This will irritate your users who will wonder why half their text is missing and it will drive them crazy and they will complain to you that you build faulty apps.

 

The issue is with the field receiving the data, it is set to a max length of 100.

  1. Find that field and change it to a suitable length like 500 or 1000 or something.
  2. Next get rid of the left truncate.
  3. Once you have found the field, set the text box for that field to only allow the set max characters you have set for that field.
  4. Next put a label under the text box that checks the Length of that text box 'Text' attribute so it gives a character count to the user
    1. Or be smart and do some calculations to show the remaining character count allowed for that text box so the user can see how many characters they have left as they are typing.

This is the best way to do it ... not deleting half of the users text.

Kind of old news. Long since solved.  Nice ideas. All workable. 

I was mostly commenting on the bad diagnostics I was getting at the time. The error messages provided by PowerApps should have been clearer.  

On the other hand, ensuring that string length maximums (and data types) are consistent between on-screen controls and back-end data stores is hygiene that I, as a developer, need to pay attention. 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (37,961)