Formula Help

(0) Share

Report

Posted on by LR-11111531-0

I have a formula that is supposed to be taking the qty of the item in the shopping cart and only creating another line(s) if the qty >1, however, even if I have a qty of 1, I'm still getting two lines in my table.

Does anyone know where in this formula the problem is at?

// Initialize a variable to store the generated Request ID
Set(
    RequestID,
    Blank()
);
// Create the initial record to capture the Request ID
Set(
    RequestID,
    Patch(
        'Equipment Requests',
        Defaults('Equipment Requests'),
        {
            'Equipment Qty': 1,
            'Equipment Category': LookUp(
                'Equipment Categories',
                EquipCategories = First(CartItems).EquipmentCategory
            ),
            // Reference the first item in CartItems
            'Notes/Comments': First(CartItems).EquipmentNotes,
            Job: ctx_seljobitem,
            // Reference the form control directly
            'Cost Code': txt_ER_CCValue.Text,
            // Reference the form control directly
            'Date & Time Needed': DateValue(
                Text(
                    dp_ER_DateValue.SelectedDate,
                    "[$-en-US]yyyy-mm-dd"
                )
            ) + TimeValue(Text(dd_ER_HourValue.Selected.Value & ":" & dd_ER_MinValue.Selected.Value)),
            // Combine date and time
            'Anticipated End Date': dp_ER_EndDateValue.SelectedDate,
            // Reference the form control directly
            'Delivery Contact Name': txt_ER_ConNaValue.Text,
            // Reference the form control directly
            'Delivery Contact Phone #': txt_ER_ConNuValue.Text// Reference the form control directly
        }
    ).'Request ID #'
);
// Iterate over each item in the cart
ForAll(
    CartItems,
    // Check if the qty is greater than 1
    If(
        Value(EquipmentQty) > 1,
        // Create multiple rows for items with qty > 1
        ForAll(
            Sequence(Value(EquipmentQty)),
            Patch(
                'Equipment Requests',
                Defaults('Equipment Requests'),
                {
                    'Request ID #': RequestID,
                    'Equipment Qty': 1,
                    'Equipment Category': LookUp(
                        'Equipment Categories',
                        EquipCategories = EquipmentCategory
                    ),
                    'Notes/Comments': EquipmentNotes,
                    Job: ctx_seljobitem,
                    'Cost Code': txt_ER_CCValue.Text,
                    'Date & Time Needed': DateValue(
                        Text(
                            dp_ER_DateValue.SelectedDate,
                            "[$-en-US]yyyy-mm-dd"
                        )
                    ) + TimeValue(Text(dd_ER_HourValue.Selected.Value & ":" & dd_ER_MinValue.Selected.Value)),
                    'Anticipated End Date': dp_ER_EndDateValue.SelectedDate,
                    'Delivery Contact Name': txt_ER_ConNaValue.Text,
                    'Delivery Contact Phone #': txt_ER_ConNuValue.Text
                }
            )
        ),
        // Create a single row for items with quantity 1
        Patch(
            'Equipment Requests',
            Defaults('Equipment Requests'),
            {
                'Request ID #': RequestID,
                'Equipment Qty': 1,
                'Equipment Category': LookUp(
                    'Equipment Categories',
                    EquipCategories = EquipmentCategory
                ),
                'Notes/Comments': EquipmentNotes,
                Job: ctx_seljobitem,
                'Cost Code': txt_ER_CCValue.Text,
                'Date & Time Needed': DateValue(
                    Text(
                        dp_ER_DateValue.SelectedDate,
                        "[$-en-US]yyyy-mm-dd"
                    )
                ) + TimeValue(Text(dd_ER_HourValue.Selected.Value & ":" & dd_ER_MinValue.Selected.Value)),
                'Anticipated End Date': dp_ER_EndDateValue.SelectedDate,
                'Delivery Contact Name': txt_ER_ConNaValue.Text,
                'Delivery Contact Phone #': txt_ER_ConNuValue.Text
            }
        )
    )
);
// Clear the cart and forms after submission
Clear(CartItems);
UpdateContext({ctx_seljobitem: Blank()});
Reset(txt_ER_CCValue);
Reset(dp_ER_DateValue);
Reset(dd_ER_HourValue);
Reset(dd_ER_MinValue);
Reset(dp_ER_EndDateValue);
Reset(txt_ER_ConNaValue);
Reset(txt_ER_ConNuValue);
Reset(txt_ER_QTYValue);
Reset(cb_ER_EquCatValue);
Reset(txt_ER_NotesValue);
// Notify the user of successful submission
Notify(
    "Request submitted successfully",
    NotificationType.Success
);
Navigate(
    'Equipment Request Form',
    ScreenTransition.None
);

Categories:

Using Apps Using Formulas

I have the same question (0)

All responses (2)

Answers (0)

MichaelFP 1,845 Super User 2025 Season 2 on at

Like (0)

Report

Formula Help

Hi!

where did you set the variable EquipmentQty with the quantity?

Was this reply helpful? Yes No
Suggested answer

jrletner 720 Super User 2025 Season 2 on at

Like (0)

Report
Formula Help

The issue seems to be in the structure of your ForAll loop. Currently, it’s iterating over every item in CartItems and creating an entry regardless of the quantity. If EquipmentQty is 1, it’s still creating a single record outside of the nested ForAll block. Let’s refine the logic to ensure that it creates one record only when EquipmentQty is 1.

Let's attempt a different approach:

Move the initial Patch statement (for quantity = 1) outside of the ForAll loop that iterates over CartItems.

Modify the conditional within the ForAll loop so that it only creates records when EquipmentQty > 1.

Try this code and see if that helps with the issue you're experiencing.

// Initialize a variable to store the generated Request ID
Set(
RequestID,
Blank()
);

// Create the initial record to capture the Request ID for items with qty = 1
Set(
RequestID,
Patch(
'Equipment Requests',
Defaults('Equipment Requests'),
{
'Equipment Qty': 1,
'Equipment Category': LookUp(
'Equipment Categories',
EquipCategories = First(CartItems).EquipmentCategory
),
'Notes/Comments': First(CartItems).EquipmentNotes,
Job: ctx_seljobitem,
'Cost Code': txt_ER_CCValue.Text,
'Date & Time Needed': DateValue(
Text(
dp_ER_DateValue.SelectedDate,
"[$-en-US]yyyy-mm-dd"
)
) + TimeValue(Text(dd_ER_HourValue.Selected.Value & ":" & dd_ER_MinValue.Selected.Value)),
'Anticipated End Date': dp_ER_EndDateValue.SelectedDate,
'Delivery Contact Name': txt_ER_ConNaValue.Text,
'Delivery Contact Phone #': txt_ER_ConNuValue.Text
}
).'Request ID #'
);

// Iterate over each item in the cart
ForAll(
CartItems,
If(
Value(EquipmentQty) > 1,
// Create multiple rows for items with qty > 1
ForAll(
Sequence(Value(EquipmentQty)),
Patch(
'Equipment Requests',
Defaults('Equipment Requests'),
{
'Request ID #': RequestID,
'Equipment Qty': 1,
'Equipment Category': LookUp(
'Equipment Categories',
EquipCategories = EquipmentCategory
),
'Notes/Comments': EquipmentNotes,
Job: ctx_seljobitem,
'Cost Code': txt_ER_CCValue.Text,
'Date & Time Needed': DateValue(
Text(
dp_ER_DateValue.SelectedDate,
"[$-en-US]yyyy-mm-dd"
)
) + TimeValue(Text(dd_ER_HourValue.Selected.Value & ":" & dd_ER_MinValue.Selected.Value)),
'Anticipated End Date': dp_ER_EndDateValue.SelectedDate,
'Delivery Contact Name': txt_ER_ConNaValue.Text,
'Delivery Contact Phone #': txt_ER_ConNuValue.Text
}
)
),
// If qty is 1, skip this since it's already added in the initial record above
Blank()
)
);

// Clear the cart and forms after submission
Clear(CartItems);
UpdateContext({ctx_seljobitem: Blank()});
Reset(txt_ER_CCValue);
Reset(dp_ER_DateValue);
Reset(dd_ER_HourValue);
Reset(dd_ER_MinValue);
Reset(dp_ER_EndDateValue);
Reset(txt_ER_ConNaValue);
Reset(txt_ER_ConNuValue);
Reset(txt_ER_QTYValue);
Reset(cb_ER_EquCatValue);
Reset(txt_ER_NotesValue);

// Notify the user of successful submission
Notify(
"Request submitted successfully",
NotificationType.Success
);
Navigate(
'Equipment Request Form',
ScreenTransition.None
);

Was this reply helpful? Yes No