Updating related fields

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.

John Wilmans 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 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.