0

Relational database design and functionality advice - linking groups of records at once

I am new to Ninox. I will do my best to get the terminology correct but please bear with me and I apologise if I have not followed some forum rules - I haven't been able to find any rules or guidance for using the forums! Apologies too if my one and only other post was a bit of a rant about the documentation. Hopefully it was constructive!

I am trying to design a database that will allow me to track my equipment hire inventory. One key function I aim to achieve is to be able to issue full itemised equipment dispatch lists that give item serial numbers and replacement values for hirer's insurance purposes. I also hope to generate Carnets for temporary export of equipment which will be organised slightly differently with items distributed into boxes with total weights for each box listed. Later I hope to enable functions like item tracking (via check in and check out processes), booking/availability with a calendar view, tracking of PAT testing, business intelligence with reports on cost vs income genreated etc. But for now just generating a printable itemised list of all items included in a hire is where I'm at.

I have managed a simple Item list to record all items in with their serial numbers, replacement values, weights, place of manufacture etc. I also have Clients (company names and addresses) in a table and a projects table that is a child table of the Clients one. 

I have also created a DeliveryNote Table that will hold the information which each dispatch list and carnet will be generated from / recorded in.

 

Screenshot 2021-07-01 at 16.49.38 

 

Each new rental order will be added as a record in the DeliveryNotes Table and from there I link to all the individual items on the Items table that make up that order.

However, since there are hundred of items and I mostly rent pre-set packages of items I want to add this into my database/app design somehow, so that for any new order I can select multiple packages of items quickly, add odd individual items from outside the packages if required, and then generate itemised list of everything and for carnets assign items to boxes.

I suspect I might have to add some new relationship table to enable this functionality, but I can also see the details I want already by drilling down inside the DeliveryNotes form view, I just can't seem to organise it, print it or get it to function how I want so a user can simply select a package and have all the items in it link.

Can anybody offer any help and direction?

 

Many thanks,

Q

5 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Q -

     

    That is a lot to do. Let us start small. Let us tackle your package issue.

     

    You are correct you will need a new table. In fact you will need two new tables. One could be called Packages and another called PackageContents. The reason for the PackageContents is you need a table that is a many to many relationship between Package and Items. I'm guessing you can have many Packages with many Items that can have overlap. Meaning Package1 can have items 1 and 2 and Package2 can have items 2 and 3 and Package3 can have items 1,2,3 and 4, etc.

     

    You can have PackageContents be a Composition (or child) of Package. A record in PackageContents can not exist without a Package.

    Screen Shot 2021-07-02 at 08.21.33

     

    Here is a quick screenshot of what it could look like:

    Screen Shot 2021-07-02 at 08.20.19

    The first column shows the Package name and the 2nd column shows the items assocaited with the package.

     

    Let us know if that helps with the package issue and then we can move on from there.

     

    Since this looks to be business related, depending your schedule you may want to look into hiring a Ninox partner to get you up and running.

    • Generation D Ltd
    • Quentin_Brown
    • 2 yrs ago
    • Reported - view

    Thanks for this extremely thorough and helpful explanation of the first step.

    I feel like I might slowly be getting the hang of some areas of Ninox now. It is a little different from other Database software I have used and there are lots of functions that you might normally have to explicitly add, built in to regular features and that requires a different way of thining, it seems.

    Before receieving your reply I settled on using a similar approach to what you suggested with Packages and package contents but for my Delivery notes table and have added a child Rented Items table to it, as shown below. I also renamed the Items table to Rental Stock for clarity.

    Screenshot 2021-07-03 at 14.14.36

    This all seems to work well so far at a basic level, structurally, although there are the odd little interface/interaction tweaks I'm curious to achieve like the fact that it currently lets me select the same Rental Stock record multiple times for any one Delivery Note but each Rental Stock record represents a single unique item in rental stock, along with it's serial number so there's only one of them to be rentaed at once. (To use the examples from you mock up, each Rental Stock record represents a particular drill or saw rather than a generic type of drill or saw of which there are many).

     

    I also find the current UI a bit clunky in that I have to drill down 2 pages (or that's the way it works as default from how I have it set up so far) to choose an item from Rental Stock when I'm on the Delivery Note view. I also have to do this one by one for every item and can't multi select. What would be an improvement would to be able to multiselect items from the stock directly on the Delivery Note form view. (In other databases I have used the UI/views and all interaction with the database were very clearly deliniated as a separate construct from the database, which existed in the background as it's own process that you designed ways of interacting with, I'm finding the automatic creation of views/forms etc is taking some getting used to and have yet to understand what is possible in terms of designing the methods of interacting with the database, and the mechanics of the default methods offered.)

     

    That aside, the next step is where it gets a bit more complicated and very dependant on deeper knowledge of how Ninox works and best practices. How would I go about letting the user select from packages and or individual Rental Stock Items that aren't part of packages, on the Delivery Note form then be able to pull up a complete itemised list for a delivery note, maybe with headings for packages? To complicate it further, how could I assign items to boxes for a packing list which summarises each box with a total weight and then includes an itemised list of contents immediately below, followed by the next box, etc? And would it be possible to build an intuitive and quick to use UI that lets you click and drag multiple items from the rental into a box to assign them?

    Phew. Sorry lots of questions. I might see if I can arrange a free "How to" session with a specialist as has been offered by Ninox when I signed up but anything that anyone can offer in terms of help here would be appreciated in the mean time and will surely help fill out the ready answers available to others who follow me.

     

    Many thanks,

    Q

    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Q -

     

    Well you do have lots of questions. I don't think I can answer all of them. But I can give you an idea of a solution to a small part of your problem.

     

    If you want to modify the list of Items to only show items that haven't been selected, you will need to use the Contraints section of the Rental Stock, if you didn't rename it. reference field in Rented Items.

     

    But before we can jump into that we need a field in Rented Items that gathers all of the items that have already been selected, since you said an Item can only be rented out once. So in Rented Items we need to create a formula field that says:

     

    let xItems := (select RentedItems);<-creates a variable that selects all records in RentedItems
    concat(xItems.Items.Text)<-concatenates all of the items based on the field called Text

     

    Here is what it looks like:

    Screen Shot 2021-07-03 at 12.33.50

     

    This is one record in Delivery Note. It has 3 items and the forumla shows all three items.

     

    Here is what my Items table looks like:

    Screen Shot 2021-07-03 at 12.35.42

    Then we can go into the Rented Items table and edit the Rental Stock reference field. Click on More Options and put this in the Constraits part:

     

    not contains(a.Formula, text(b.text))

     

    The letter "a" points to the Rented Items table, while the letter "b" points to the Items (Rental Stock in your DB). So what this says is show records from Items (Rental Stock) that are not in the Formula field.

     

    Now if I click on a record in Rented Item and click on the Item field I only see:

    Screen Shot 2021-07-03 at 12.37.16

     

    The big issue is that with this method you have to clear out or some how mark Delivery Notes that are no longer valid or Items that have been returned.

     

    I'll keep poking around to solve this part as well.

    • Generation D Ltd
    • Quentin_Brown
    • 2 yrs ago
    • Reported - view

    I figure there must be various ways to achieve soe of the things I'm asking about above. Some directions to explore that spring to mind include scripting dynamic choice fields populated by the list of packages, probably looping through the list of items in the selected package and adding each of them in turn as linked records to new records in the rented items table. I would then need a way of accounting for the package it comes from for use in generating summary reports and also need a method for assigning the items to boxes ( a further complication is that a lot of packages would come pre-configured with their own boxes but other loose items will need assigning to an existing or new box).

     

    I'm thinking that box assignments could be handled in the rented items table with a field there which assigns each record to a box, though I have been thinking that the boxes themselves could be specially designated items on the Rental Items table, perhaps through a Yes/No field to say if the item is a box or not, but I'm not sure if this would make things more difficult or not and I should maybe have a separate table for them.

     

    Are these good directions to explore or are there other ways using Ninox's unique toolset that would be better or easier to implement?

     

    (and boy I wish I could edit my previous posts, reading through all the typos and unclear sentences...)

    • Generation D Ltd
    • Quentin_Brown
    • 2 yrs ago
    • Reported - view

    Thanks again Fred. 

    Your answer only popped up for me after I posted my follow up question at 10:06pm, because I hadn't refreshed the page...oops!

    That looks like a possible route to a solution. I was wondering if there was something native in Ninox that made this simpler,  or if maybe linking tables in the other direction would do it. But I guess from your response that this is outside of normal use cases and something custom has to be built for it.

     

    Ninox seem to have thought about a lot of common use cases in their built in tools and I appreciate the way that parent and child tables work to fulfill the kind of common use for join tables in things like invoices and my Delivery notes. They automatically provide a lot of functionality and structure you might otherwise have to manually build yourself to make the relationship between those tables work in that way.

Content aside

  • 2 yrs agoLast active
  • 5Replies
  • 499Views