0

NEWBIE - CONFIGURING ONE-TO-ONE AND AUTO UPDATING

AFFILIATED GMRS REPEATERS USERS

DATABASE DESIGN NOTES FOR NET CHECK-INS

September, 2023

Software:  Ninox – v 3.9.3 downloaded free from Apple App Store, single computer only.

            Data only on local hard drive for simple start, but later can be shared in Ninox Cloud

for future use by more than one member.  Ninox Cloud $ 10.70/mo.  20% discount?

 

TABLES & FIELDS

3 Tables:  Repeater Users Roster, Radio Net Operators & Radio Net Check Ins.  Red indicates Requiredfield.   Green indicates default.  Italics and indent indicate linked tables/fields.

 

Repeater Users Roster -- Fields/Columns:

            Tab – Users Roster Color Green 

            ID

            First Name – Text

            Middle Name – text

            Last Name – Text

            Nick Name - Text

            FCC Call Sign - Text 8 characters

            Exp. Date – Date

            Unit ID - Text

            Affil Date – Date

            Affil Type – Choice:  Regular|Auxiliary|Probationary|Applicant|Auth. Pers. User|Drop

            Type Date -- Date

Address line 1 – Text

Address line 2 – Text

            Str Name – Text

City --  Text

            State – Text (Choice Drop Down IL IND WIS MICH)

            Zip Code – Number 5 + 4 digits

            Primary E-Mail – Email

            Main Phone – Phone

            2nd E-Mail – Email

            2nd Phone -- Phone

            ID Picture -- Image

                        Radio Net Operators

                        Radio Net Check Ins

 

 

 

Radio Net Operators -- Fields/Columns:

            Tab – Net Operators Color Blue

            Id

                        Repeater Users Roster

                        Type Date

            Status – Choice: Active|Inactive (radio buttons)

            Name—Text

                        City - Text

                        FCC Call Sign

            

 

Radio Net Check Ins -- Fields/Columns:

            Tab Net Check Ins Color Purple

            Id

            Date of Net – Date

            Repeater Users Roster

                        Affil Date

                        FCC Call Sign

                        Name

                        City

            Channel – Choice: .675|.650|.700|Web|Relay (switch default)

            Radio Type – Choice: Control|Mobile|Portable (radio buttons)

            Additional – Choice: Early|Late|New (radio buttons)

            Remarks- Text

            

 

I am band new to Ninox and databases.  I’ve read most parts of the Ninox Documentation and watched quite a few of the Ninox and NinoxUS tutorials so far.  I have 2 questions at this point of my efforts.    Radio Net Operators table is a subset of Repeater Users Roster.  Only some Users are Operators.  But all Operatorsare Users.  And all Operators have additional data fields.

 

Questions:       1)  Relationship from Repeater Users Roster to Radio Net Operators is not one-to-many, it is one-to-one.  How do I configure this?  I saw something somewhere saying a 3rd table in between is necessary to tie each of the 2 table records together, but now I can’t find this again.

 

2)  When Roster data is edited/changed in Users the linked columns in Operators should be updated at the same time.  Does this happen as soon as the tables are linked?  Or, should I make the linked Roster table to Operators a Composition to then make Operators a child of Roster in order to make edits in Rosterautomatically appear in the columns linked into Operators?  Or does this require trigger after update, and, if so, does this have to be done for each linked in field?  How do I make Operators stay up to date automatically when Users is edited?

Any responses/suggestions would be most appreciated!

12 replies

null
    • simon
    • 10 mths ago
    • Reported - view

    Hi Randy - welcome to Ninox. It would probably be easiest to work this out if you uploaded a archive of your database.

    Best

    Simon

      • Randy
      • 10 mths ago
      • Reported - view

       Thanks for your reply, most recent archive attached :) 

    • simon
    • 10 mths ago
    • Reported - view

    Hi Randy - it looks to me that you don't need a table for "Radio Net Operators" - you can simply have a field (probably a multiple choice field) in the "Repeater users" to mark as Operator/Call. You can have a separate view in the Repeater users table to show just this.
     

      • Randy
      • 10 mths ago
      • Reported - view

       Thanks for your reply.  However, of the perhaps 250 Users, only about 50 will also be Net Operators thus needing additional fields.  So that's a lot of wasted empty fields for most Users who don't need them.

      In reading the  Ninox Documentation or one of their early tutorials, I saw a graphic and some text about configuring one-to-one relationships with a interfacing table in between.  Each record appeared once on each side of this table.  They even mentioned a specific name for such a table but I can't find this again, perhaps because the tutorials are not workings so I can't go back and view them again.   Is anyone aware of this who can give me more details?  Thanks.  

      • Randy
      • 10 mths ago
      • Reported - view

       Another question, if I may.  What would happen if I left Users-->Operators as a one-to-many, but only actually used it as a one-to-one?  Would that cause any problems?  What if I made Operators a child of Users, and Net CheckIns a child of Net Operators?  Thanks for your insight!

    • Fred
    • 10 mths ago
    • Reported - view

    Welcome to Ninox.

    As said, you probably don't need another table since Radio Net Operators. Like you said, the two tables would have a 1:1 relationship which makes me wonder why create a separate table.

    You need a separate table when you find yourself tracking multiple instance of the same data. For example, a user could have multiple email or phone number you might be tempted to create email1 and email2 or home phone, work phone, mobile. Instead you could create a new child table called Contacts and that table can track all of the ways you can contact the person.

     

     said:
    When Roster data is edited/changed in Users the linked columns in Operators should be updated at the same time.  Does this happen as soon as the tables are linked? 

    It depends on how you setup your tables. In the following setup we have TableA and TableB and they are linked with a reference field in TableB.

    If you create regular fields in TableB (text, number, date, etc). Those fields do nothing but hold data.

    You can create formula fields in TableB that look through the link to get data in TableA, so whenever data is changed in TableA it will show up in TableB.

    or

    You can create Triggers (at the table or field level) or buttons in TableA that will then write the data to fields in TableB.

      • Randy
      • 10 mths ago
      • Reported - view

      Thanks a lot for the suggestions about updating.  I'll try to look at tutorials on formula fields and triggers.

    • simon
    • 10 mths ago
    • Reported - view

    I can't quite see the problem:

    "However, of the perhaps 250 Users, only about 50 will also be Net Operators thus needing additional fields.  So that's a lot of wasted empty fields for most Users who don't need them."

    What further data do you need to add to Operators? It would be easy enough place those fields in a tab that's hidden in non-operators. You'll either have fields you don't need, or an empty table relationship. I've uploaded an illustration, with a tick box to mark 'operator'. 

    I think  this "I saw a graphic and some text about configuring one-to-one relationships with an interfacing table in between.  Each record appeared once on each side of this table." may be with regard to how to set up a many-many relationship, where the standard in Ninox is to add a linking table.
     

      • Randy
      • 10 mths ago
      • Reported - view

       Thank.s so much for your time to respond.  I downloaded your archive and opened it to take a look.  I didn't see a new check mark field for non-operators, but I understand what you have explained.  I've searched for the graphic I saw before, but can't find it, you must be right that it related to many-many.  Thanks again for your suggestion, this is a lot to digest just starting out and I appreciate all the guidance I can get.

      • simon
      • 10 mths ago
      • Reported - view

       They're there! The extra tab is only visible if the "operator" field" is checked. As is the title bar.

    • John_Halls
    • 10 mths ago
    • Reported - view

     There isn't a 1:1 relationship available to Ninox natively. Use a 1:many with the many side being the optional additional fields. You could write a script to prevent additional records from being created on the many side.

    I would favour the additional fields being included in the main table with all the fields being collected together under a tab that is conditionally shown or hidden depending on the status of a flag. The days have long gone when you had to worry about the empty fields taking up space. Good structure with yield far more benefit.

    Regards John

      • Randy
      • 10 mths ago
      • Reported - view

       This is very helpful.  I will need to educate myself further to understand how to configure "conditionally shown or hidden on the status of a flag" (yes/no field?).  I'll look at more tutorials to figure this out, thanks a lot for pointing me in the right direction.

Content aside

  • 10 mths agoLast active
  • 12Replies
  • 129Views
  • 4 Following