I have three types of data and therefore three apps: Presenters, Programs and Program Instances (occurring on specific dates).
Presenters can lead many Programs, Programs can have one or more Presenters, Programs can repeat and be lead by different Presenters on different dates.
What I’m trying to figure out is how to create Many to Many connections in Kintone. I would like to create a new Program Instance and then connect one Program to it (LookUpFieldA) and 1 to 10 Presenters to it (LookUpFieldB) but the lookup field only allows one connection.
Also, this is puzzling me:
“Please note: if data in a record in the datasource app is updated, the record in the destination app will not automatically update. You must navigate back to the destination app, edit the record, click Lookup again, and click Save.”
Doesn’t this defeat the purpose of a relational database?
Thanks for your help!
You can build One-to-One(1:1) and One-to-Many(1:M) relational models with kintone but I am afraid that Many-to-Many(M:M) cannot be created with the current kintone features.
As you know, “lookup field” is for 1:1 model. For 1:M, you can use “Related Records field” and/or create “table”.
“Related Records field” shows the record(s) that match the condition you specify from the same or another App.
You can place “Lookup field” in the table, so you can select the data from another App.
The following links explain about “Related Records field” and “Table”.
What is “Related Records”?: https://get.kintone.help/k/en/user/app_settings/form/related_records/whats_relatedrecords.html
Configuring a Related Records Field: https://get.kintone.help/k/en/user/app_settings/form/related_records/set_relatedrecords.html
How to Create a Table: https://get.kintone.help/k/en/user/app_settings/form/table.html
Regarding the lookup field at the destination App that does not update automatically, you are right that you need to update the record for the field at Destination App that has been updated at source App.
If you want kintone to do automatically, you can customize your App with API(PUT). Or with the current basic feature, you can mass update using a CSV file.
Please see the link about API.
This is written in Japanese:https://developer.cybozu.io/hc/ja/articles/204537310-%E7%AC%AC11%E5%9B%9E-kintone-REST-API%E3%82%92%E5%88%A9%E7%94%A8%E3%81%97%E3%81%9F%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E6%9B%B4%E6%96%B0
Here is how to mass update Lookup field data using CSV file.
Set “Prohibit duplicate values check box” for the Key field in the data source app.
Export records from file at Destination App
Please refer the following link.
*At Step 4, select “Include header row”.
*At Step 7, only select “Record Number field” and “Lookup field”.
Update the data on the CSV file just created
Import records from file at Destination App for mass update
Please refer the following link.
Hope this helps.
kintone isn’t a relational database.
It has two features that fetch data from another table (App), but it is not a relational database.
Feature 1: Look-up field
Treat this field, as a field that just helps you to fill in multiple fields in one go.
By entering 1 set of data, the lookup field will fill in the rest of the fields (the field mappings).
This only copies data, and does not copy any information about relationships.
Feature 2: Related Records field
This feature looks at a given field value and condition, and then displays all record data that match the condition. Note that this only displays data, and does not save any sort of data into the record. Every time you load the browser, it will give back a new list that matches the given condition.
Now, for your scenario, I would create 3 kintone Apps.
"Presenters" would contain info of the presenters - name, hire date, gender etc.
"Programs" would contain info of the program details - name, details etc.
"Program instances" is where you would place lookup fields inside, to get data from the Presenters App and Programs App.
As there would be multiple presenters, you would want to place the lookup for presenters in a table.
I made a gif of what the data entry looks like for the Program instances.
I’m not sure much about your scenario, but I don’t really see a need for these being a relational database.
Is there a case where you would need to change a field in the Presenters or Programs DB ,to have it reflected on to the Program instances?
And one more thing.
You could also go back to your Programs DB to set up a Related Records field.
This will pick up the Program instances related to that Program.
Akira, using an “instances” app is a brilliant approach! It solved my problem.