0

Search Bar with Button

Hello,

 

For weeks, I have been trying to create this formula, but have not had any luck (I am also not sure if this is possible. 

 

In this instance, I am working with 3 tables: Invoices, Products, and Items.

 

The invoices does what it says, it creates invoices. The products table is just a stock table of all of the products sold. Items is the table that connects to our products table, and invoices together. For example, when I want to add a line to the invoice, I create a new linked record from items. Within items, it also grabs products. 

 

The reason why I have the Items table, instead of just liking products directly to the invoice, is because within the Items table, I added the ability to override the prices to reflect on invoices. When the price is overridden, it will only affect the linked invoice, and not any other invoices. Whereas if I changed the price to Products, it would recalculate every invoice. (Perhaps their could possibly be a better way for this, but this is what I came up with)

 

Anyways, I am trying to create a linked to products table within Invoices, lets name this linked table for example "SEARCH". My goal when you choose a product from the SEARCH table, and click a button, it creates a new Item and links what was chosen into search in the field in Items. Then it voids (clears) out the search table for the next item to be added.

 

The formula I need assistance for is the button to does all of the leg work.

 

While creating my own formula, I had some success, only when I add a something to the search, and click the button, it changes the values of the other linked Items as well.

 

let Main := Id;
let CRI := (create Items);
let SRC := Search.Id;
CRI.(Invoices := Main) + Items.('Products := SRC)

19 replies

null
    • SMoore
    • 5 yrs ago
    • Reported - view

    Or it would be even nicer to put this in the trigger after update field, where the initial formula could be: if Search then __formula___ end

    • Leonid_Semik
    • 5 yrs ago
    • Reported - view

    let Main :=this;

    let SRC:=Search;

    let CRI:=create Items;

    CRI.Invoices:=Main;

    CRI.Products:=SRC;

    Search:=0

    • SMoore
    • 5 yrs ago
    • Reported - view

    This is AMAZING! Thank you so much! 

    • Robert_Traykoski
    • 5 yrs ago
    • Reported - view

    Hi S Moore,
    I am very interested in your technique. Could you explain to me a little more about how you create a Linked table Search and that you reset after your search. A screenshot if you want to understand is welcome.
    Thank you in advance for sharing.

    Best Regards.

    Robert

    • SMoore
    • 5 yrs ago
    • Reported - view

    Would love to! This is something that would change the way you use Ninox. I created a Computer Repair Database to test out the capabilities with Ninox, and I was able to make this program fuly functional with Ninox, where a small repair shop could use this!

     

    The search bar I've added, will work when searching for a product in it. Once clicked, it addes it to the "Items" field, and then clears out the search for the next item (with trigger after update, instead of clicking a button each time). The only downfall, if you have a barcode scanner, you would still have to click the item after scanning it in the search.

     

    Screen Shot 2018-10-11 at 8.37.15 AM 

    Screen Shot 2018-10-11 at 8.41.03 AM

     

    What it basically does is when searching, it will create a new record, in my case the new record is called Items, and it will attach "Products/Services" to the new Items record. 

     

    The formula I am working with under the search bar trigger after update is:

    if Search then
    let Main := this;
    let SRC := Search;
    let CRI := (create Items);
    CRI.(Repair := Main);
    CRI.('Products/Services' := SRC);
    if Search.Name = "Custom Part Order" or Search.Name = "Discount/Adjustment" or Search.Name = "Refund" or Search.Name = "Coupon" or Search.Name = "Computer Hardware Part" or Search.Name = "Return/Recycle CR" or Search.'Custom Price' = true then
    popupRecord(record(Items,number(CRI.Id)))
    end;
    Search := 0
    end

     

    If you look, I also have it set to where if an item that involves a custom price to be set, the record will automatically popup, instead of just add to the invoice. 

     

    Hope this helps!

    • blackie
    • 5 yrs ago
    • Reported - view

    Thanks. I think this will be useful to me. I am going to try and implement it.

    I currently have an Orders table that links directly to Products. Using an Items table is a much better idea.

    • SMoore
    • 5 yrs ago
    • Reported - view

    Yes, I would suggest using the products table as your stock table, and using a table Items to link your Orders and Products together. That way if you need to change the quantity, or override the price, you have the capabilities. 

    • Nick
    • 5 yrs ago
    • Reported - view

    I'm sorry @S Moore but i don't understand! Is different than typical model?

    Screen Shot 2018-10-11 at 18.44.50

    Search is a 4th table related to Products? And Items don't related to Products?

    Thanks in advance!

     

    Nick

    • SMoore
    • 5 yrs ago
    • Reported - view

    Exactly! Now within Invocie, create a link to Invoice Items renamed to Search, then under trigger after update, insert the following code (note, you may want to change Invoice Items to Composition: No):

    if Search then
    let Main := this;
    let SRC := Search;
    let CRI := (create 'Invoice Items');
    CRI.(Invoice := Main);
    CRI.(Product := SRC);
    Search := 0
    end

    Hope this helps!

    • Robert_Traykoski
    • 5 yrs ago
    • Reported - view

    Hi S Moore,

    Thanks for your sharing. Have a nice days. Robert

    • Nick
    • 5 yrs ago
    • Reported - view

    @S Moore...

     

    Screen Shot 2018-10-15 at 21.43.00

     

    Any ideas?

     

    Nick

    • SMoore
    • 5 yrs ago
    • Reported - view

    Try setting composition to Yes, and try your code again. 

    • Nick
    • 5 yrs ago
    • Reported - view

    Same error!

    • blackie
    • 5 yrs ago
    • Reported - view

    Is the link in Invoice Items Table back to Invoice Table named "Invoice"?

    I was able to implement this code, but my Invoice table is named Orders, and my Invoice Item table is named items

    Orders->items->Products

    My link back to the Orders table from items is named Order. If I use "Orders" in my script, I get the same error you are seeing.

    Screen Shot 2018-10-15 at 4.48.39 PM

    This the script I am using. I also copy the price from Products to the new item, and have a checkbox that will popup the new record if it is chekced.

    if 'Product Search' then
       let Main := this;
       let SRC := 'Product Search';
       let CRI := (create items);
       CRI.(Order := Main);
       CRI.(Product := SRC);
       CRI.(price := SRC.Price);
       if text('Popup new item') = "Yes" then
           popupRecord(record(items,number(CRI.Id)))
        end;
    'Product Search' := 0
    end

    • GrünTAG
    • Christian_E_Larrea
    • 5 yrs ago
    • Reported - view

    @smoore said "The only downfall, if you have a barcode scanner, you would still have to click the item after scanning it in the search."

     

    S Moore, thanks fopr your insight!  Now, upon what's quoted, I have a Symbol scanner that you can program to integrate a prefix after scanning, such as 'enter' or 'tab'.  This probably avoids you to still have to press the item.  What do you think?

    • GrünTAG
    • Christian_E_Larrea
    • 5 yrs ago
    • Reported - view

    @smoore said "The only downfall, if you have a barcode scanner, you would still have to click the item after scanning it in the search."

     

    S Moore, thanks fopr your insight!  Now, upon what's quoted, I have a Symbol scanner that you can program to integrate a prefix after scanning, such as 'enter' or 'tab'.  This probably avoids you to still have to press the item.  What do you think?

    • SMoore
    • 5 yrs ago
    • Reported - view

    @christian@fixit.ec sadly, even though the scanner can insert 'enter' or 'tab' it still will not automatically add the item. 

    If you want a copy of the Ninox search database, I added it to the Webinar EN 2018 shared group.

    I can also email you it directly. 

    • GrünTAG
    • Christian_E_Larrea
    • 5 yrs ago
    • Reported - view

    Thanks S Moore! Can you please email me the shared folder link or DB, it would be of great assistance! christian@fixit.ec

    • SMoore
    • 5 yrs ago
    • Reported - view

Content aside

  • 5 yrs agoLast active
  • 19Replies
  • 8114Views
  • 1 Following