0

Production: should storage tanks share a table or each have their own?

I need some advice regarding the basic logical layout/relationships of my database. I'm tracking production and inventory in a distillery. I have a table for distillations, a table listing the storage tanks, and a table of transfers between tanks. Product can be transferred between storage tanks or to or from a distillation. 

Currently a formula field in the storage tanks table is adding up all the transfers in and out to get the current volume. One of the problems I'm running into is that it would be very complicated to get the volume stored at points in the past such as the start and end of February.

My best guess is that it would be easier to give each of the tanks their own table with each record being a transfer. That way I could use a progressive sum (https://forum.ninox.com/t/x2hrtc9) to show the volume after each transfer.

Am I on the right track? Does anyone have any suggestions?

7 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Alex

    No, your tanks need to be records in a single table. I would have three tables, Bins to hold the tanks and production areas (distillations?), Transfers to hold each transfer and Inventory to hold the inventory. Transfers have two links to Bins, BinFrom and BinTo plus the Date and Qty. Inventory has a link to Bin plus the Date, Qty and Total. Have a button on Transfers with the following script 

    let a := this;
let b := BinFrom.Qty - Qty;
BinFrom.(Qty := b);
let c := (create Inventory);
c.(
    Bin := a.BinFrom;
    Date := a.Date;
    Qty := a.Qty * -1;
    Total := b
);
let d := BinTo.Qty + Qty;
BinTo.(Qty := d);
let e := (create Inventory);
e.(
    Bin := a.BinTo;
    Date := a.Date;
    Qty := a.Qty;
    Total := d
)

    This will keep track of all your transfers and the quantity present in any of your tanks on a given day. It creates two Inventory records for each Transfer, one for the transfer out of a Bin and one for the transfer into a Bin. It keeps track of the total quantity at all times.

    If these are the only tables you are using then include two virtual Bin records 'In' and 'Out' for initial transfers into your business, and final transfers out. If you are looking for further development of your system please feel free to ask.

    Regards John

      • Alex_Mumm
      • 2 yrs ago
      • Reported - view

      John Halls Thanks John! It took me almost a day to wrap my head around it, but I got it working in the end. 

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Alex Mumm Great, well done Alex. Any problems, just ask.

      • Alex_Mumm
      • 2 yrs ago
      • Reported - view

      John Halls I've run into a problem running this code on an old/slow computer. It seems like it's (often, but not always) skipping the section of the code to create the new record. However, the other parts of the code seem to be working consistently, like the BinTo.(Qty := d).

      I've considered setting up some sort of error checking to give me a message if the new record wasn't created by checking that the table Id number increased. However, that would mean I'd have to manually enter a lot of records. Would something like below work? 

      let a := this;
let c := last(select ABC).Id;
let b := (create ABC);
let d := number(last(select ABC).Id) - number(c);
while not d do
    let b := (create ABC);
    let d := number(last(select ABC).Id) - number(c);
end;
b.(
    NumberA := a.Number;
    'Date + Time' := now()
);

      Haven't tested it. I'm a bit worried about that while loop never ending.

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Alex Mumm Hi Alex Looking at your recent posts it looks like there is something going on that is not limited to this bit of code. You could try wrapping it in a do as server command.

      Your error checking code will give your old computer more grief than the code it's checking. With three select commands, one of them inside a loop, it could cause problems.

      • Alex_Mumm
      • 2 yrs ago
      • Reported - view

      John Halls I found the source of my problem. I had the Create New Records set to Admin only for a couple of tables. That meant that non-admin users couldn't create new records even when using the button with the above code in it. A bit embarrassing. That too far too much troubleshooting.

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Alex Mumm Good find Alex.

Login to reply

Content aside

Categories

View all topics
  • Status Answered
  • 2 yrs agoLast active
  • 7Replies
  • 135Views
  • 2 Following