0

Updating related fields

Hi there. I am new to Ninox and am trying to build an app for my business. We do boat charters.  I don't even know if this is possible.

I have a tabel "Stocklist". This is updated by the child table "Inventory". I have got this working well. I have used this code to duplicate the relevant field in the Stocklist and then delete the old one.

let newId := (select Stocklist).Id;
let newAmt := 'Number left in Stock';
let newItem := Item.Item;
let newSales := Item.'Sales price';
let newRec := (create Stocklist);
let b := Item.Id;
newRec.(
    'Amount in stock' := newAmt;
    Item := newItem;
    'Sales price' := newSales
);
delete (select Stocklist where Id = b)

What I am wondering is: 1.) Is this code efficient? 2.) Is there a way to link this update to an already booked appointment with guest names?

 

So an enquiry comes in. The customer details are captured in a table called "Customers". I also have a tabel called "Boats" for the boat the charter will take place on. Then I have a table called "Bookings" where I put the customer and the boat as an appoinment. These are all linked.  

 

Now, on a charter they may have a few bottles of drink and some food. I would like to be able to add the drinks etc to their table so I can keep a record of: their charter, which boat they were on and what they ate and drank.

 

Is this possible? Or is is too much for Ninox? I have tried messing around with creating new records and then deleting but the relationship links are broken?

 

Thanks for any help (if possible)

 

John

4 replies

null
    • Fred
    • 8 mths ago
    • Reported - view

    I would like to be able to add the drinks etc to their table so I can keep a record of: their charter, which boat they were on and what they ate and drank.

    This can be done. One way would be to add a child table to Bookings, maybe call it Ordered Items. Then you can link another table, maybe your Stocklist or Inventory or a table that stores all things that can be ordered for a booking. Then you can track what is Ordered for each booking.

     said:
    I have a tabel "Stocklist". This is updated by the child table "Inventory". I have got this working well. I have used this code to duplicate the relevant field in the Stocklist and then delete the old one.

     I don't quite understand your Stocklist table and Inventory table relationship. Is Stocklist all items that you could have in stock, and your Inventory is what you actually have in stock. If that is true then Inventory should never be affecting Stocklist, but the other way around.

    I also am not clear why you need to duplicate a record in Stocklist then delete the older record. Why not just update the record?

    I have tried messing around with creating new records and then deleting but the relationship links are broken?

    You need to keep track of the record Id so you can recreate the links that are necessary.

    Let us go through the code you provided. I'm guessing this code is in a button/Trigger in the Inventory table.

    let newId := (select Stocklist).Id;
    let newAmt := 'Number left in Stock';
    let newItem := Item.Item;
    let newSales := Item.'Sales price';
    let newRec := (create Stocklist);
    let b := Item.Id;
    newRec.(
        'Amount in stock' := newAmt;
        Item := newItem;
        'Sales price' := newSales
    );
    delete (select Stocklist where Id = b)

    Why do you have line 1 when you don't use it anywhere else? You never want to do a select statement if you don't have to. I'm not sure what you want to do with line 1 since you are asking for the record Id of ALL records from Stocklist.

    Line 3: Is the first Item a reference field to Stocklist? What kind of field is the second Item?

    Line 5: you create the new record in Stocklist and now have that record Id stored in the variable newRec.

    Lines 7 - 11, there is where you link the new record in Stocklist to the current record in Inventory. What is the reference field name in Stocklist that points to Inventory?  You need to add that to the list of field names, add a new line that captures the current record, and then link them. Something like:

    let curRec := this;
    newRec.(
       Inventory = curRec;
    );
    

    Once you reach line 12 you delete the record in Stocklist then you lose all links to all Inventory records that also have that link. You don't go through and fix the other records in Inventory that have links to the soon to be deleted Stocklist record.

    But this can all be avoided by NOT deleting the record and just updating the info.

    • John_Wilmans
    • 8 mths ago
    • Reported - view

    Aw Fred you legend. Thank you for your help. Let’s go through it all one step at a time. 
     

    Firstly. Stock list is where all of the items are kept. So 20 bottles of champagne, so many bottles of white wine, etc. I have tried to have a way (using the inventory form) where a hostess can go in and use a bottle of wine on the charter and then update the table from that form. This way if the amount of wines gets low it is shown.  
     

    Line 1 has been left in there by mistake. It WAS relating to another bit of code I’ve deleted and forgot to delete that one. 
     

    You have said something in your last sentence that would be AWESOME!  Can I simply update a record?  That’s what I’ve been trying to do for weeks!

    if I could use a form to update the Stocklist, Client and Booki by forms, then life would be good!  Can you do this?  It would simplify everything. 
     

    thanks again for your help and the detailed answers.  It’s great learning despite the frustration. 

    • Fred
    • 8 mths ago
    • Reported - view
     said:
    I have tried to have a way (using the inventory form) where a hostess can go in and use a bottle of wine on the charter and then update the table from that form. This way if the amount of wines gets low it is shown.  
    if I could use a form to update the Stocklist, Client and Booki by forms, then life would be good!  Can you do this?  It would simplify everything.

    You should look into Dashboards, here

    , here

    and here.

    Once you start having non-DB people use the DB then you need to start controlling the interaction. I think it is best never to have regular users interact with your data directly. The dashboard is a way to create a step where you can check data is valid and consistent before you add/modify anything. In addition it allows you to make sure people don't interact with data that they shouldn't.

    It does mean adding a few things to your DB, but it is well worth the time. I use dashboards all the time and I'm the only user of my DB. :)

    I see that Stocklist has a field called Amount in Stock and Inventory has a field called Number left in Stock. That seems like you are storing the same data in two different places.

    To me, it seems like Stocklist should only track the items that you could sell, beer, wine, veggie platters, etc. No need to track amount here, but here is where you could track vendor.

    Then your Inventory is where you track items that are for sale and amounts.

    But to take your example, how to update info in Stocklist from Inventory. If you have a button in Inventory you can update Amount in Stock with Number left in Stock:

    let t := this;
    Item.'Amount in Stock' := t.'Number left in Stock'
    

    This is of course assuming that the reference field in Inventory to Stock is called Item.

    • John_Wilmans
    • 8 mths ago
    • Reported - view

    Aw Fred. Thank you so much for your help and explanations. You’ve given me something to think about. 
     

    I never thought of using a dashboard almost like an order form. I will look into that. And who knew updating a record could be so easy!!

     

    thabks so much for your help man. 

Content aside

  • Status Answered
  • 8 mths agoLast active
  • 4Replies
  • 54Views
  • 2 Following