Table References and Relations

Hello Glenn,
check out the tutorial videos:
https://ninoxdb.de/ninox/en/tutorials.html
There is one about how to create relations.
Best regards, Alex

When I look at what things are made visible by clicking on "More options" , I do not see "Composition". I see "Display field only, if:", "Trigger after update" and "Global unique identifier", and that is all. Am I doing something wrong?

Hi Giralua,
we moved that option to the top right of the field settings, it's no longer in "More options" - because users didn't find it. We'll fix the documentation.
Frank

I still cannot find it. Please show breadcrumbs.

OK, I found it. Thank you.

My last database program had the capabilities to use a look-up table to save keystrokes and prevent typing errors. I can't find this ability in the tutorials. Am I overlooking it or does that capability not exist.

Example: Type HAG in the "look-up" field and it will return information from the linked table "Hagerstown, MD 21750 USA"

Thanks,

Hi Allen,
linking data from related tables will open a dialog with the table info and a type box. Typing will apply a filter to the choices.
There are also two search options: the global search can be accessed with the magnifier on top. This search is applied on all tables of the database.
The local search can be accessed from within a table. And only releases information from this table.
Let me know if this helps.
Best regards, Alex

So, I think you're saying "NO", there are no Look-Up tables available. What I was hoping for was to have information from one table copied to another table based on a formula.

I respectfully request this function be considered for future releases. Thanks!

Hi Allen,
it might work a bit different than in your previously used database software, but I think a simple table reference will do the job (at least for the example you stated): Let's assume you have a table Shipping and another table Destination where Destination has a text field with values like "Hagerstown, MD 21750 USA". Then you could simply create a table reference from Shipping to Destination which will create a field in the shipping form to look up Destinations or create new ones.
If you need something more sophisticated, you could use triggers to copy data. Please have a look at this example:
https://www.dropbox.com/s/8vy0bni1y61ajin/Lookup-Field.ninox?dl=0
It doesn't use table references but instead utilizes a trigger which looks up the value from the Destinations table and if not found automatically adds it to the Destinations.
Best regards
Frank

From the example database at
https://www.dropbox.com/s/8vy0bni1y61ajin/Lookup-Field.ninox?dl=0 that you referred Allen to, it is clear that Ninox has a query language that can be used in formulas. I can't find documentation for those language features on the website. Is it documented somewhere? If so, could you post links?

By the way, I did successfully use the query feature in a database of my own.

Thanks.

Hi Gerrie,
you'll find more information regarding the language at:
http://manual.ninoxdb.de/en/function-and-language-reference/
Best regards,
Frank

Hello, I have a table that relates locations to contacts and productions with contacts going to call logs that reference a production, and more.

My question is, is it possible to relate a contact to multiple locations? For example, there are many locations that can fall under one city office so the same person would be the primary for more thane location.

I cannot seem to find a way to allow that o happen. As a result, a have added a second contact to the location in case the primary one is already associates with w location, but it gives me 'contact2'.

Thank you for your help. I absolutely love Ninox!! I would only make suggestions about the ability to drag & drop for custom report layouts and be able to include to actual picture rather just a reference to the file name.

Have a nice day!

Lisa in Atlanta, GA

Hello Lisa,

you can attribute one contact to many locations if you create a reference from "contacts" to "location".

If you like to attribute several contacts to several location, you can use a n:m relation to achieve. Create a new table "relations" and link this table to "contact" and "location". With this relation you can link a contact to multiple locations.

Best regards, Alex

I am testing ninon in the server version. I have an invoicing spreadsheet that I wish to migrate to Ninox (if it works)

The invoicing template you provide seems to do the job except I cannot seem to be able to retrieve item prices from my price reference table the way I would like to. In my spreadsheet, I had a column for each year. Prices would then be looked up in the correct column according to the date of the invoice. This was to keep an archived reference of "older" prices.

I Know it is possible to "paste" the values of the reference table with triggers but I would like to be able to do this while not having to alter my reference table, other than adding a new line/ column of prices each year.

Is this possible (if I have made my request clear enough...)

Tanks

Christian

Hi Christian,
thanks for giving Ninox Server a try!
The way the invoice template works is that it stores the price per invoice item to avoid that changing an articles price also changes invoices.
Of course, you can also add a price history to articles:
- Create a new table named Article Price
- Add a reference to Article with Composition=Yes
- Add a number field Year
- Add a number field Price
Set the trigger on Invoice Item's field Article to

let y := year(Invoice.Date);
Price := Article.'Article Price'[Year = y].Price

Regards
Frank

I need some help. I have two tables and need to create a relationship between them. I watched the video on how to do this, but relating the records manually is impractical.
One table has 61000 records.

How can I have the relationship happen automatically when values in a key field in each table match?

Assuming I can do that, then how do I show all unmatched records from one of the tables?

Thanks,

Steve

Hi Steven,
Here is a video

https://www.youtube.com/watch?v=6YOtUFE1dEA

regards
Leo

Hi,

I've watched tutorial videos and understand the relationship models—but something is still not making sense, or at least not working for me.

My data model is as follows: I've got a got a music compositions table consisting of my works (list of things likes composition name, recording, attributes, etc.). Another table list opportunities to sell my works (e.g. licensing for film, TV, etc.). A third table is a long list of genres that is to be used to tag entries in the other two tables.

I've tried a couple of referencing methods: for example in the music opportunities table, I want to reference compositions from the compositions table as possible tracks I might want to sell for any given opportunity (e.g. record). I set this up as a link TO a reference, where I link from the Opportunities table to the Compositions table. It sort of works: I'm able to select one or more compositions from the compositions table and have them show up in the opportunities records. The PROBLEM is this: once I've selected a composition in one opportunities record, it no longer is available in other records. This makes no sense to me. It's (in my mind) similar to and Invoice/Products reference, where each invoice can have multiple products. When you start a new invoice, you would still be able to choose products that had already been used in other invoices, right?

OK, so when I tried it the other way, e.g. linking FROM one table to the other, it sort of work, but sort of didn't. I did this: In my opportunities table, I wanted to choose from the list of musical genres in that genres table. I did it as a link from. It does allow me to choose multiple genres in multiple opportunities records without removing the genres that have already been chosen in one record. However, it shows the Opportunities table as a subset below the genres table in the sidebar. That doesn't seem right.

Clearly, I'm missing a step (or two) here, but I can't figure it out. Thanks for your help!

If you could send me a copy of the Ninox database you are working on (Main menu -> "Save Archvie as...") I'm sure that I can find a solution to your problem.

My mail address: support@ninoxdb.de
Cheers
Birger - Ninox Support

Thanks, I'll send shortly.

Hi, I sent you the database archive a few days ago. Please let me know if you can resolve the issue.

Thanks.

hello I have a table with Prospects with 2 subtables (Member of the boards and shareholders). If i click on a prospect I can see detailed info on Boards and Shareholders. Now I would like, when I click on a board member to have detailed information on all board where he may be participating, how can I achieve that?
Basically on the Board member subtable I could have several records for the same person (same person code) with different companies (prospect code)
thanks

Hi sbaloz,
you'd need to set up the schema as follows:
- Table Prospects
- Table Contact
- Table BoardMember
- linked to Prospects (Composition: Yes)
- linked to Contact
With this schema, a Contact can be board member of several boards.
Best regards
Frank

Hi, thanks for a wonderful and efficient db application.

Is it possible to concat only the unique values from a table reference?

With best regards,

Adriaan

Hi Adriaan,

you can use unique:

concat(unique(Values))


Best regards,
Frank