RoSoft_Steven Fred

Changes Made:

1. Created a new field for the cart status:
   We updated the CartClosed field to allow three possible values:

   • 1 — cart is open,
   • 2 — cart is closed,
   • 3 — cart is open or undefined (used if the cart has not yet been finalized).

2. Following your advice and Fred’s suggestions:

   • Checking the cart status field: We implemented the advice from the forum and Fred’s suggestion to consider three values for the CartClosed field (true, false, and null), and adjusted the check to account for all three possible states.
   • Checking for an existing invoice: We added a check to search for open invoices with CartClosed set to 1 or 3, which helps identify existing open carts and avoid creating new ones if one is already open.

Issue:

Despite the changes we made, the code still creates a new invoice each time the "Add to Cart" button is clicked, even if an open cart already exists.

let selectedClient := this.Clients;  // Get the selected client from the current form
let xNewProduct := this.Products;  // Get the selected product from the current form
let xNewQuantity := this.Quantity;  // Get the selected quantity from the current form

// Check if there is an open invoice (CartClosed is either 1 or 3)
let openInvoice := first(selectedClient.Sales[CartClosed = 1 or CartClosed = 3]);

// If an open invoice exists, check if the product is already in the invoice
if openInvoice != null then
    let existingProduct := first(openInvoice.Sales_tab[Products = xNewProduct]);  // Look for the product in the current invoice

    // If the product exists, increase the quantity
    if existingProduct != null then
        existingProduct.('Quantity1' := existingProduct.'Quantity1' + xNewQuantity);  // Add the quantity to the existing quantity
    else
        // If the product doesn't exist in the invoice, create a new entry in Sales_tab
        let newSailTab := create Sales_tab;  // Create a new record in Sales_tab
        newSailTab.('Sales' := openInvoice);  // Link the new Sales_tab record to the current invoice (openInvoice)
        newSailTab.('Products' := xNewProduct);  // Link the product to the new Sales_tab record
        newSailTab.('Quantity1' := xNewQuantity);  // Set the quantity for this product in the new Sales_tab record
    end
else
    // If no open invoice exists, create a new invoice
    let newInvoice := create Sales;  // Create a new invoice in the Sales table
    newInvoice.('Date' := today());  // Set the date for the new invoice to today
    newInvoice.('CartClosed' := 3);  // Set CartClosed to 3 (meaning the cart is open or undefined)
    newInvoice.('Clients' := selectedClient);  // Link the new invoice to the selected client

    // Create a new entry in Sales_tab for this new invoice
    let newSailTab := create Sales_tab;  // Create a new record in Sales_tab
    newSailTab.('Sales' := newInvoice);  // Link the new Sales_tab record to the newly created invoice
    newSailTab.('Products' := xNewProduct);  // Link the selected product to the Sales_tab record
    newSailTab.('Quantity1' := xNewQuantity);  // Set the quantity for this product in the new Sales_tab record
end;

// Reset the product and quantity fields after adding to the cart
this.Products := null;  // Clear the selected product field
this.Quantity := null;  // Clear the selected quantity field

Database Structure:

1. Clients Table:

   • This table contains information about the clients, including their details (name, contact information, etc.).
   • The Sales field within the Clients table is a linked table that stores the client’s sales records (invoices).

2. Sales Table:

   • The Sales table is a sub-table within the Clients table. Each record in this table represents a single invoice or cart.
   • The CartClosed field in the Sales table indicates whether the cart is open, closed, or has an undefined value (3 possible states: 1 = open, 2 = closed, 3 = undefined).

3. Sales_tab Table:

   • The Sales_tab table is another linked table within Sales that tracks individual products in each sales record.
   • Each record in the Sales_tab table represents a product added to an invoice, with fields for Products(product), Price  and Quantity1 (quantity).

How the Tables Are Linked:

• When I click the "Add to Cart" button, a record is created in the Sales_tab table, which is linked to the specific invoice in the Sales table.
• When I click the "Add to Cart" button, the system first checks if there is an open invoice (a record in Sales with CartClosed set to 1 or 3).
• If an open invoice exists, the system checks if the product is already in the Sales_tab. If the product is there, its quantity is updated. If not, a new entry is created for that product in the Sales_tab.
• If no open invoice exists, a new Sales record is created for the client, and a new entry is added in the Sales_tab.

Fred - rid(162), fixed everything and so far everything works without problems. Thank you very much. Happy New Year, best wishes from me

let selectedClient := this.Clients;
let xNewProduct := this.Products;
let xNewQuantity := this.Quantity;
let openInvoice := first(selectedClient.Sales[CartClosed = 1 or CartClosed = 3]);
if openInvoice != null then
    let existingProduct := first(openInvoice.Sales_tab[Products = xNewProduct]);
    if existingProduct != null then
        existingProduct.(Quantity1 := existingProduct.Quantity1 + xNewQuantity)
    else
        let newSailTab := (create Sales_tab);
        newSailTab.(Sales := openInvoice);
        newSailTab.(Products := xNewProduct);
        newSailTab.(Quantity1 := xNewQuantity)
    end
else
    let newInvoice := (create Sales);
    newInvoice.(Date := today());
    newInvoice.(CartClosed := 3);
    newInvoice.(Clients := selectedClient);
    let newSailTab := (create Sales_tab);
    newSailTab.(Sales := newInvoice);
    newSailTab.(Products := xNewProduct);
    newSailTab.(Quantity1 := xNewQuantity)
end;
this.(Products := 0);
this.(Quantity := null)

There was one problem with the filter, can't set it up. But after the New Year, I'll do it)

Jacques TUR all done and working, formula below. Thank you, happy new year to you, best wishes

RoSoft_Steven all done and working, formula below. Thank you, happy new year to you, best wishes