0

Can I replicate Access key fields, indexes, enforced relationship integrity, and data entry options in Ninox

I’m trying to replicate a personal-use database that I originally developed in Access 97, and the transferred to FileMaker Pro. I’ve been able to import the data successfully, except into Choice fields, and have looked at enough views to form the opinion that I can produce any information ‘reports’ that may be required. It was when I started looking at data input that I realized that I really need to find a copy of “Ninox for Dummies”. In a so far futile attempt to solve my problems, I created a small database containing two tables:  Parent (one field. - ParentName) and Child (two fields - ParentName and ChildName). The Data Model is shown below. 

 

The constraint in the Link to Parent field is ‘a.ParentName = b.ParentName’, where ‘a’ refers to the Child table.

The following is a picture of the ParentChild table, and it illustrates three problem areas. 

 

Problem 1. Big3 (#5) is an entry in ParentChild, but is not an entry in Parent. I need ParentName entries in Child to be restricted to the entries in Parent.

 

Problem2. Big2 - Small21 (#3 and #4) is entered twice. I must restrict entries in Child to unique combinations of ParentName and ChildName.

 

Problem 3. When I’m entering data in the ParentName field, It like it to be in a lot box. From what I’ve seen in the on-line manual, this is only possible in a Choice field, and this seems to present its own problems. Is there another way.

21 replies

null
    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Sorry, didn't figure ot to insert pictrue proberly. Model shows ling from ParentChild to CHild. ParentChild shoes current data in ParentCHild, and I'd like to enter data in a 'list' box, not a 'lot' box.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I really need spell check option.

    • Fred
    • 2 yrs ago
    • Reported - view

    I agree that spell check on the forum would be great.

    Since I can't see the pics,

    1) is your ParentName reference field in the Child table set to be a composition of Parent? If so then when you open up a record in parent you should see a subtable where you can add records in your Child table from your Parents table. If you do it this way all your records will be properly linked. This will also make sure no records are created in Child that are not linked to Parent.

     

    2) I'm sure someone has figured out away to popup a dialogue box that will warn you if you have duplicates. I'll check around or maybe someone else can answer.

     

    3) You can change how Ninox shows the ParentName field. If you click on the field and click on Show As you can change how it shows up in the Child table. Let us know if you don't see that option.

     

    Ninox for dummies would be a good idea, but sadly it doesn't exist and Ninox's own manual is not very good.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Sorry to take so long answering. COVID.

    In response to your first remark

    The Data Model picture that I tried to enter (PDF) was similar to that shown page 46 of the downloaded manual, which shows a 1:N relationship. I suppose that I could also use ‘Ninox Forums for Dummies’. The constraint ‘a.ParentName = b.ParentName’, where ‘a’ refers to the Child table. I added the following data to the ParentChild table. I currently don’t see any significant advantage to going with the Composition relationship.

     

       Parent Child

    1  Big1  Small11

    2  Big1  Small12

    3  Big2  Small21

    4  Big2  Small21

    5  Big3  Small13

     

    Having added the ‘Link to Parent’ field in ParentChild, and entered what I thing is the appropriate constraint, I don’t understand why I can enter ‘Big3’ in ParentChild when it isn’t a record in Parent. I don’t know how this Link to/from is supposed to work. Does there have to be a field that is common to both tables? Definitely a requirement is SQL and Access database design, but I haven’t seen anything that suggests that it’s a requirement in Ninox. So, I’m still faced with trying to solve my data integrity, as in entering only ‘allowed’ values in a Child table. I\My current plan is that my actual database will have three parent tables, two M:N tables, and one transactions history table, but I’m going to have to solve problem 1 first, then move on to ‘list box’ entry option in various Child fields.

    Second remark - I’m looking, but am too old to sift through more than 800 pages (in some cases) in the forum.

    Third remark. I don’t see the ‘Show As’ option when clicking on the Parent field (admin mode on) or in edit fields. I’m using the web based version of Ninox.

    I know - probably can’t buy ‘Ninox Forums for Dummies’ either.

    • Fred
    • 2 yrs ago
    • Reported - view

    Ok, first let us look at your tables. You said you have a Parent table with a field called Names. I'm guess it is a simple text field (it has a T icon). If you created the link correctly you should also see a field called ParentChild with a table icon next to it.

     

    Now in your ParentChild table, you have a field called Name (also probably a text fields with a T icon). You also have a field, I think, called ParentName. Does this have an arrow pointing AT the name?

     

    Is this true?

     

    In Ninox you don't have to create a field in both tables and then link the fields. You just have to create a reference field (Edit Fields > Create Table Reference) and Ninox will link the records once you select the appropriate records.

     

    One reason to make the tables a composition is to improve your workflow. So if you have a new Parent. Your current workflow is to enter it in the Parent table, then switch to the ParentChild table then enter in records there? If ParentChild is a child of Parent you only need to create the Parent then in the same table create the child records.

     

    So when you enter a ParentChild record, you click on the Parent name field, what do want to see there? I don't think the contraints will do anything since you have no link set. For example, I have a show jumping db. In my results table after I have selected the team name, when I click on the rider field it only shows me the riders of that team. Then when I click on horse, it only shows me the horses that the rider has previously jumped with. But with your db a new ParentChild record has no link to Parent by default. So there is no relationship to limit the Parent field, so you will always see all Parents.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Thanks for answering, and taking the time to do so. I think that I've confused things (certainly me) by trying to make things to simple, and not adequtely explaining what I'm trying to do, and why I'm trying to do it. I'll try to post that information tomorrow.

    Speaking of trying, I'm going to try and post the Model, that i was referring to earlier, in three picture formats and see if any of them work.

    JPEG

     

    PNG

    Model_PNG

    TIFF

    • Fred
    • 2 yrs ago
    • Reported - view

    You are correct that the forum only supports picture formats, so your earlier PDFs don't show properly.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I’m attempting to duplicate parts of a personal use data base that I originally created in MS Access (V2.0, I think) which I ‘migrated’ through various Access versions and then into FileMaker. I’ve moved to a Mac M1, so am looking for an affordable DBMS. My option, at the moment, seems to be Ninox, although it appears that LibreOffice is moving forward fairly quickly.

    The structure that I’m thinking of implementing consists of tables in three categories: parent, which are those tables that contain the only allowable values that can be entered in certain fields in the database; N:M tables, which consist of two ‘child’ fields from the relevant parents; and, transaction, which are the tables in which routine entries are made, and which store the ‘history’ of the transactions.

    There are three ‘parent’ tables, which could have a number of fields, only one of which as relevant. My tables in this category, which have only one field, are: Providers, field Provider; Categories; field Category; and, Services, field Service.

    There are two M:N tables, each of which has two fields with the same name as the fields in the relevant ‘parent’ table, and two ‘Link to” parent fields. These tables are: ProviderCategory and CategoryService. I’ve also created constraints in all “Link to’ fields, which have the format ‘a.fieldname = b.fieldname’, where ‘a’ represents the ‘child’ table and ‘b’ the parents. For example, the constraint in the “Link to Provider’ field in ProviderCategory is ‘a.Provider = b.Provider’ with ‘a’ being ProviderCategory.

    There is one transaction table, which is Payments. The Payments table contains a number of fields that appear to be ‘problem free’, three ‘child’ fields which have the same name as the ‘parent’ table fields, and three ‘Link to’ parent fields.

    I’ve created a database, the model for which is shown below

    Nope - still having difficulties posting. Where do I get "Ninon Forums for Dummies"?  

    I don't see the need for any composition set up, unless that is dictated by preferences regarding data entry options. Everything else can easily be solved by using different views and applying appropriate filters.

    I have imported CSV files into this database, and am able to create any necessary reports using views, filters, and sort fields. My immediate concerns are related to data integrity and data entry (ease of).

    First, as noted before, the ability to 'identify' key field(s) and/or create indexes (unique or otherwise.) These are essential features of any relational DBMS that I've look at. There must be someway they can be implemented in Ninox.

    Second, data integrity. In my current database, the only values that can be enterd in a Child field are those that have been entered in the related Parent field. Consider the following.

     I've entered one record in the ProviderCategory table. Provider is "Why is this " Category is "possible?"

    My problem is that 'Why is this ' has not been entered in the Providers table and "possble?" has not been entered in the Categories table. I thought that the constraint (higlighted in Bold above) on the 'Link to Providers' field in ProviderCategory would prevent this, but it didn't. Nothing in the manual.

    Third, 'ease of entry’. I would like to emulate the listbox (which includes an 'autocomplete capability) control in my current database.

    Am grateful for any advice.

    • Fred
    • 2 yrs ago
    • Reported - view

    Hi there -

     

    I hope I can add some clarity.

     

    You say: There are three ‘parent’ tables, which could have a number of fields, only one of which as relevant. My tables in this category, which have only one field, are: Providers, field Provider; Categories; field Category; and, Services, field Service. There are two M:N tables, each of which has two fields with the same name as the fields in the relevant ‘parent’ table, and two ‘Link to” parent fields.

     

    So I recreated your DB:

    Screen Shot 2021-05-15 at 16.28.12

     

    Now I have the following setup:

    Screen Shot 2021-05-15 at 16.30.15

    Screen Shot 2021-05-15 at 16.30.30

    Screen Shot 2021-05-15 at 16.30.41

    Screen Shot 2021-05-15 at 16.30.52

    Screen Shot 2021-05-15 at 16.31.01

    In Ninox, the M:N tables only need three fields to work. 1) a field that is describes the record, then the two reference fields to your other tables. When I enter a record in one of the M:N tables I see this:

    Screen Shot 2021-05-15 at 16.37.28

    When I click on either LinkToCategories or LinkToServices I see all my records in the corresponding table, this is what the LinkToCategories shows:

    Screen Shot 2021-05-15 at 16.38.45

    As you can see Ninox creates the linkage for you without having to create a field called ProviderID to use to link your records. In Ninox you just create a reference field and Ninox creates the link. That is the one thing that I really like about Ninox and will ruin me if I were to ever switch to a more traditional DB.

     

    Does this help with the linking part? Or am I going over stuff you already know?

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    thanks, Fred. Will give it a shot. Definately not sstuff I already know, certainly notin working with Ninox!

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Tried it. It works, and actually restricts entries to combinations of record values that are already entered in in the parent tables. I assume that the ‘Name’ entry should have something to do with M:N relationship - I.e. if I’m creating a relationship between “Fred’s Pharmacy’ and ‘Prescription Drugs’, I should probably enter something like ‘FredsPrescriptions’ as opposed to ‘Name37’.

    Now that I’ve got it, how do I use it?

    In Access, the Payments ‘table’ might look like a table, but it would be a form, and the ‘fields’ would actually be form controls, with Provider, Category, and Service being list boxes. 

    I could create a query that allowed me to select the values in ‘Category’ field of ProviderCategories where the value in the ‘Provider’ field equaled the value selected in the ‘Provider’ field in the Payments table, and use this query as the source for the Category listbox. A similar query would be created as the source for the Service listbox. So, if I selected ‘Bob’s Restaurant’ in the Provider ‘field’, my choices in Category could include ‘Burger’ and ‘Pizza’, but not ‘Prescription Drugs’, and if I select ‘Burger’, my options could include ‘Cheese’ and ‘Double’, but (probably ) not ‘Marijuana’.

    As I asked previously, how to I use my new, fancy one text field, two link to fields M:N table to implement the entry restrictions I’ve outlined in the previous paragraph. I ave no clue.

    You’re going to regret responding to my initial question, Fred. Too late, I’ve got your number now.

    • Fred
    • 2 yrs ago
    • Reported - view

    Let me see if I can help out here.

     

    Now we will add a new table called Payments. Add a number field called Amount, or whatever. Then add a reference link to Payments, Categories, and Services.

    Screen Shot 2021-05-17 at 08.52.44

    Don't worry about the spaces. They were added just to make the view "pretty".

    Screen Shot 2021-05-17 at 11.06.31

     

    Then under Categories you would add this under contraints:

    Screen Shot 2021-05-17 at 08.53.27

    So how to read this, well we have to remember that we have a M:N table called ProviderCategories. In that table is a reference field called LinkToProvider. This table is our only link between Categories and Provider. Since the reference field in Payments points to Categories, when you edit the field Ninox thinks you are working in the Categories table. So you then use fields in Categories to constrain the Categories field in Payments.

     

    Then you do the same with Services, but that is based on Names in Categories:

    Screen Shot 2021-05-17 at 08.53.46

    So now after I have selected a Provider, the Categories field will only show me Categories that match the Provider selected. The same will hold true for Services after I have selected a Category.

     

    In my DB, I track team show jumping results. I have the following tables:

    Teams with a child Team Membership

    Riders

    Horses

    Partnership > M:N table of Riders and Horses

    Locations

    Results > with reference fields to Teams, Riders, Horses, Locations

     

    Recently I was entering in results in my Results table. So when I enter in data, I have to select the event location, the team, the rider, and the horse. The league has 16 teams and each team fields two rider/horse combo each round. And they each jump two rounds, so that is a lot of clicking the same location.

     

    Then it dawned on me that I should be entering in my results through the Location table.

    Screen Shot 2021-05-17 at 11.15.03

    I click on the +Create record button and Ninox brings up the first view from the Results table and now I can enter in data, but I can skip the Location as Ninox has created the link for me.

     

    In my Results table, after I have selected a team, the Riders list will only show me riders from that team. Then my selection of horses is based on a table called Partnership.

     

    I hope this helps. I'm sure I'm missing something and others smarter than me can fill in the holes.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Thanks again. Will definately try it, but will have to wait until tomorrow (at the earliest) as I'm currently mulit-taskig the covid restrictions.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I’m attempting to duplicate parts of a personal use data base that I originally create in MS Access (V2.0, I think) which I ‘migrated’ through various Access versions and then into FileMaker. I’ve moved to a Mac M1, so am looking for an affordable DBMS. My option, at the moment, seems to be Ninox, although it appears that LibreOffice is moving forward fairly quickly.

    The structure that I’m thinking of implementing consists of tables in three categories: parent, which are those tables that contain the only allowable values that can be entered in certain fields in the database; N:M tables, which consist of two ‘child’ fields from the relevant parents; and, transaction, which are the tables in which routine entries are made, and which store the ‘history’ of the transactions.

    There are three ‘parent’ tables, which could have a number of fields, only one of which as relevant. My tables in this category, which have only one field, are: Providers, field Provider; Categories; field Category; and, Services, field Service.

    There are two M:N tables, each of which has two fields with the same name as the fields in the relevant ‘parent’ table, and two ‘Link to” parent fields. These tables are: ProviderCategory and CategoryService. I’ve also created constraints in all “Link to’ fields, which have the format ‘a.fieldname = b.fieldname’, where ‘a’ represents the ‘child’ table and ‘b’ the parents. For example, the constraint in the “Link to Provider’ field in ProviderCategory is ‘a.Provider = b.Provider’ with ‘a’ being ProviderCategory.

    There is one transaction table, which is Payments. The Payments table contains a number of fields that appear to be ‘problem free’, three ‘child’ fields which have the same name as the ‘parent’ table fields, and three ‘Link to’ parent fields.

    I’ve created a database, the model for which is shown below

     Screen Shot 2021-05-10 at 8.21.25 PNG PM

     Sorry this taking so long -I'm trying to multi-task - cut grass, shower, read forums, do this, etc.

    I don't see the need for any composeition set up, unless that is dictated by prefernces regarding data entry options. Everything else can easily be solved by using divverent views and applying appropriate filters.

    My immediate concerns are related to data integrity and data entry (ease of).

    First, as noted before, the ability to 'identify' key field(s) and/or create indexes (unique or otherwise.) These are essential features of any relational DBMS that I've look at. There must be someway they can be implemented in Ninox.

    Second, data integrity. In my current database, the only values that can be enterd in a Child field are those that have been entered in the related Parent field. Consider the folowwing.

     

    ProviderCategory PNG

    My problem is that 'Why is this ' has not been entered in the Providers table. Nothing has. I thoght that the constraint on the 'Link to Providers' field in ProviderCategory would prevent this, but it didn't. Nothig in the manual.

    And, as mentioned before, my 'ease of entry' concern is that I can somehow emulate the listbox (which includes an 'autocomplete capabilty) control in my current database.

    Am gratefull for any advice.

    • Fred
    • 2 yrs ago
    • Reported - view

    I'm confused. Back on May 16th you said things are working. Now it sounds like you are saying things are not working. Which is it? Please show what the edit fields window looks like for the Provider table.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Sorry. I'm confused at the best of times, and am now working (slowly) on both models - the dynamic Choice model and the one you documented above. A quick question - are your M:N tables the one text field - two link tos, or are they two text fields (with links, of course)? Or does it make any difference

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I've entered the following as a constraint in Payments.Category, and got a 'Field not found ...' errror when I entered 'Name' (highlighted in Bold).

    b.ProviderCategory.LinktoProviders.Name = a.Provider.Name

    My ProviderCategory has, as the moment, two Text fields ( as weel as Links to paarents) - Provider and Category.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Check out that accent - 'weel' and 'paarents'.

    • Fred
    • 2 yrs ago
    • Reported - view

    "Name" is the actual name of the field in the Provider table. I don't know what the text field name is for you, so you just need to replace it with your field name. If you reference page 1 again, you see that in the Provider table I have the text field called Name. So that is what I use in the Constraints for Payments.Category.

     

    If you go back to page 1, the ProviderCategory and CategoriesServices tables both have one text field and two reference (link) fields. Again, you have to unlearn how other DB software do relationships and learn how Ninox does it. In Ninox you don't create a field and then link it to another table. In Ninox, you create a reference field, which you can call whatever you want, to the table you want to link to. Ninox then does the heavy lifting for you. You can create multiple reference fields to the same table if it suits your needs, they just have to be named differently.

     

    If you can, please post a screenshot of the Edit Fields window of the Providers table (the window where is shows the table name and all the fields in the table.).

     

    I hope this helps.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Thanks, Fred. Will give it a try - more multitasking at the moment.

    • Customer Service Ninox
    • Ricardo_Klunter
    • 2 yrs ago
    • Reported - view

    @jmaci1987

     

    Hey I am just checking in to see whether your issue was solved already or not.

    In case that you still need support I would like to invite you to our weekly webinar. 

    Join our weekly Open office hour Webinar to ask your questions. You can find the webinars using this link:

    https://ninox.com/en/webinar

    Best regards,
    Ricardo

Content aside

  • 2 yrs agoLast active
  • 21Replies
  • 1762Views