Solved: Re: Modifying existing code to look up record on a...

jmcbee · ‎10-26-2023

I have been banging my head up against the wall on this. So my existing code works. WHat im attempting to add is when I click the save button if Checkbox1 is not selected it verifies the data in DataCardValue8_1 exist on SP list "Parts" in column "Part". If it exist follow the original code(patch) if it does not display error "Part does not exist". If Checkbox1 is selected follow original code. I have made so many changes with various outcomes. the best I have had is where almost everything works. The only issue is if the part didnt exist it just did nothing.

Here is OG code:

If(Checkbox1.Value,
    Patch(
        'Material Handling', 
        Defaults('Material Handling'), 
        {
            Title: DataCardValue8_1.Text,
            QTY: Value(DataCardValue10_1.Text),
            '700#': DataCardValue17.Text,
            '711#': DataCardValue19.Text,
            WO: DataCardValue18.Text,
            'Deliver To?': DataCardValue11_1.Selected, 
            Requestor: DataCardValue12_1.Text,
            Reason: DataCardValue13_1.Selected, 
            Comments: TextInput1.Text,
            DeliverTo: DataCardValue16_1.Text,
            RESTOCK: Checkbox1.Value
        }
    );
    Reset(DataCardValue8_1);
    Reset(DataCardValue10_1);
    Reset(DataCardValue13_1);
    Reset(DataCardValue14_1);
    Reset(Checkbox1);
    Notify("Your order has been submitted successfully"),
  If(
    IsBlank(DataCardValue12_1.Text),
    Notify("Requestor is required.", NotificationType.Error),
    Len(DataCardValue8_1.Text) < 8 || Len(DataCardValue8_1.Text) > 14,
    Notify("Part # should be between 8 and 12 characters.", NotificationType.Error),
    Len(DataCardValue10_1.Text) = 0,
    Notify("QTY is required.", NotificationType.Error),
    !(IsBlank(DataCardValue18.Text)) && Len(DataCardValue18.Text) <> 8,
    Notify("WO number should be either blank or exactly 8 characters.", NotificationType.Error),
    IsBlank(DataCardValue17.Text) && IsBlank(DataCardValue18.Text),
    Notify("Please fill in either WO or 700#.", NotificationType.Error),
    IsBlank(
      LookUp(
         BrowseGallery1.AllItems, 
         Title = DataCardValue8_1.Text &&
         (
            WO = DataCardValue18.Text || 
            '700#' = DataCardValue17.Text
         )
      )
   ) &&
   IsBlank(
      LookUp(
         BrowseGallery1_1.AllItems, 
         Title = DataCardValue8_1.Text &&
         (
            WO = DataCardValue18.Text || 
            '700#' = DataCardValue17.Text
         )
      )
   ) &&
   IsBlank(
      LookUp(
         BrowseGallery1_2.AllItems, 
         Title = DataCardValue8_1.Text &&
         (
            WO = DataCardValue18.Text || 
            '700#' = DataCardValue17.Text
         )
      )
   ),
   Patch(
      'Material Handling', 
      Defaults('Material Handling'), 
      {
         Title: DataCardValue8_1.Text,
         QTY: Value(DataCardValue10_1.Text),
         '700#': DataCardValue17.Text,
         '711#': DataCardValue19.Text,
         WO: DataCardValue18.Text,
         'Deliver To?': DataCardValue11_1.Selected, 
         Requestor: DataCardValue12_1.Text,
         Reason: DataCardValue13_1.Selected, 
         Comments: TextInput1.Text,
         DeliverTo: DataCardValue16_1.Text
      }
   );
   Reset(DataCardValue8_1);
   Reset(DataCardValue10_1);
   Reset(DataCardValue13_1);
   Reset(DataCardValue14_1);
   Notify("Your order has been submitted successfully"),
   Set(varShowPopup, true)
  ))

Here is 1 of about 100 different attempts:

If(
    IsBlank(DataCardValue8_1.Text),
    Notify("Part # is required.", NotificationType.Error),
    Len(DataCardValue8_1.Text) < 8 || Len(DataCardValue8_1.Text) > 14,
    Notify("Part # should be between 8 and 14 characters.", NotificationType.Error),
    Len(DataCardValue10_1.Text) = 0,
    Notify("QTY is required.", NotificationType.Error),
    !(IsBlank(DataCardValue18.Text)) && Len(DataCardValue18.Text) <> 8,
    Notify("WO number should be either blank or exactly 8 characters.", NotificationType.Error),
    IsBlank(DataCardValue17.Text) && IsBlank(DataCardValue18.Text),
    Notify("Please fill in either WO or 700#.", NotificationType.Error),
    If(
        Checkbox1.Value, // If the checkbox is selected, use the original code
        Patch(
            'Material Handling', 
            Defaults('Material Handling'), 
            {
                Title: DataCardValue8_1.Text,
                QTY: Value(DataCardValue10_1.Text),
                '700#': DataCardValue17.Text,
                '711#': DataCardValue19.Text,
                WO: DataCardValue18.Text,
                'Deliver To?': DataCardValue11_1.Selected, 
                Requestor: DataCardValue12_1.Text,
                Reason: DataCardValue13_1.Selected, 
                Comments: TextInput1.Text,
                DeliverTo: DataCardValue16_1.Text,
                RESTOCK: Checkbox1.Value
            }
        );
        Reset(DataCardValue8_1);
        Reset(DataCardValue10_1);
        Reset(DataCardValue13_1);
        Reset(DataCardValue14_1);
        Reset(Checkbox1);
        Notify("Your order has been submitted successfully"),
        Set(varShowPopup, true),
        If(
            !IsBlank(LookUp('Parts', Part = DataCardValue8_1.Text)),
            Patch(
                'Material Handling', 
                Defaults('Material Handling'), 
                {
                    Title: DataCardValue8_1.Text,
                    QTY: Value(DataCardValue10_1.Text),
                    '700#': DataCardValue17.Text,
                    '711#': DataCardValue19.Text,
                    WO: DataCardValue18.Text,
                    'Deliver To?': DataCardValue11_1.Selected, 
                    Requestor: DataCardValue12_1.Text,
                    Reason: DataCardValue13_1.Selected, 
                    Comments: TextInput1.Text,
                    DeliverTo: DataCardValue16_1.Text,
                    RESTOCK: Checkbox1.Value
                }
            );
            Reset(DataCardValue8_1);
            Reset(DataCardValue10_1);
            Reset(DataCardValue13_1);
            Reset(DataCardValue14_1);
            Reset(Checkbox1);
            Notify("Your order has been submitted successfully"),
            Set(varShowPopup, true)
        )
    )
)

hthpowerapper · ‎10-26-2023

In order to implement the additional verification logic you described, you will need to use a combination of the LookUp function to check if the part exists in the "Parts" list on SharePoint, and the If function to conditionally execute different logic based on whether the part exists and whether Checkbox1 is selected. Something like...

// Define a variable to hold the result of the part lookup
Set(
    varPartLookup,
    LookUp(
        Parts,  // Assume "Parts" is the name of the SharePoint list
        Part = DataCardValue8_1.Text  // Assume "Part" is the name of the column
    )
);

// Now proceed with your original logic, with the added part verification
If(
    Checkbox1.Value,
    // Original logic for when Checkbox1 is selected
    Patch(
        'Material Handling', 
        Defaults('Material Handling'), 
        {
            // ... (rest of your original fields here)
        }
    );
    Reset(DataCardValue8_1);
    // ... (rest of your original reset and notify logic here)
,
    // New logic for when Checkbox1 is not selected
    If(
        IsBlank(varPartLookup),
        Notify("Part does not exist", NotificationType.Error),
        // Original logic for when Checkbox1 is not selected
        If(
            IsBlank(DataCardValue12_1.Text),
            Notify("Requestor is required.", NotificationType.Error),
            // ... (rest of your original nested if-else logic here)
        )
    )
)

Note: The name "Parts" and the column name "Part" are assumed based on your description. You'll need to replace these with the actual name of your SharePoint list and column, respectively.

View solution in original post

hthpowerapper · ‎10-26-2023

In order to implement the additional verification logic you described, you will need to use a combination of the LookUp function to check if the part exists in the "Parts" list on SharePoint, and the If function to conditionally execute different logic based on whether the part exists and whether Checkbox1 is selected. Something like...

// Define a variable to hold the result of the part lookup
Set(
    varPartLookup,
    LookUp(
        Parts,  // Assume "Parts" is the name of the SharePoint list
        Part = DataCardValue8_1.Text  // Assume "Part" is the name of the column
    )
);

// Now proceed with your original logic, with the added part verification
If(
    Checkbox1.Value,
    // Original logic for when Checkbox1 is selected
    Patch(
        'Material Handling', 
        Defaults('Material Handling'), 
        {
            // ... (rest of your original fields here)
        }
    );
    Reset(DataCardValue8_1);
    // ... (rest of your original reset and notify logic here)
,
    // New logic for when Checkbox1 is not selected
    If(
        IsBlank(varPartLookup),
        Notify("Part does not exist", NotificationType.Error),
        // Original logic for when Checkbox1 is not selected
        If(
            IsBlank(DataCardValue12_1.Text),
            Notify("Requestor is required.", NotificationType.Error),
            // ... (rest of your original nested if-else logic here)
        )
    )
)

Note: The name "Parts" and the column name "Part" are assumed based on your description. You'll need to replace these with the actual name of your SharePoint list and column, respectively.

LaurensM · ‎10-26-2023

Hi @jmcbee,

In your second code example, you can achieve this by slightly modifying your existing If() checkbox statement:

If(
    //Your datacard value validation code here...
    Notify("Please fill in either WO or 700#.", NotificationType.Error),
    If(
        //Either the checkbox is checked or the part exists
        Checkbox1.Value || !IsBlank(LookUp('Parts', Part = DataCardValue8_1.Text)),
        //Original patch code
        Patch(
            'Material Handling', 
            Defaults('Material Handling'), 
            {
                Title: DataCardValue8_1.Text,
                QTY: Value(DataCardValue10_1.Text),
                '700#': DataCardValue17.Text,
                '711#': DataCardValue19.Text,
                WO: DataCardValue18.Text,
                'Deliver To?': DataCardValue11_1.Selected, 
                Requestor: DataCardValue12_1.Text,
                Reason: DataCardValue13_1.Selected, 
                Comments: TextInput1.Text,
                DeliverTo: DataCardValue16_1.Text,
                RESTOCK: Checkbox1.Value
            }
        );
        //Note you can also use a variable approach to reset controls
        Concurrent(
            Reset(DataCardValue8_1),
            Reset(DataCardValue10_1),
            Reset(DataCardValue13_1),
            Reset(DataCardValue14_1),
            Reset(Checkbox1),
            Notify("Your order has been submitted successfully")
        ),
        // <false section starts below>
        Notify("Part does not exist.", NotificationType.Error, 5000)
    )
)

A part I removed was 'Set(varShowPopup, true)' since I was unsure whether this needed to be part of the true or false value of your If() statement.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

jmcbee · ‎10-27-2023

Thank you, I never thought of using the !IsBlank on a checkbox! Also, I forgot that I could control all the resets by a variable. Im self taught and still learning. Thanks again!

jmcbee · ‎10-27-2023

I do want to mention, since I cant accept both answers as correct. They both were able to solve my issue and both helped me with other aspects of my code. Thank you guys for the help!