Search Bar with Button

S

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)

S

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

Ninox Profile

let Main :=this;

let SRC:=Search;

let CRI:=create Items;

CRI.Invoices:=Main;

CRI.Products:=SRC;

Search:=0

S

This is AMAZING! Thank you so much! 

R

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

S

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!

Ninox Profile

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.

S

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. 

Ninox Profile

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

S

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!

 

1 2
Reply