Hi! I'm new to Wix and Wix Code but have years (decades, really :)) of experience with other databases. I'm helping a friend who is starting a new small business and, so far, I've been able to figure out how to do most of the things I wanted to do. However, I'm stumped on this one.
The database will have a variety of collections that will be linked using reference fields. In Oracle, we'd simply call those joined tables. An example would be a table of employers and another table of employer contacts. Each contact will belong to one employer but any employer might have many contacts.
I can add employers without any problem. I have also defined a collection for employer contacts and created a reference field in there that links to the employer name in the employer collection. What I can't figure out how to do -- and can't find any documentation regarding -- is how to create a web form that will let a user add a new employer contact.
Clearly, they will need to specify which employer a contact record belongs to. They can't do that by retyping the employer name -- there could be all sorts of mistakes with that approach. Instead, they need to be able to specify which employer a contact is being created for.
I have a bunch of similar needs, all of which represent exactly the same technical issue. The solution to one is the solution to all of them.
I would appreciate guidance in any form. A reference to a manual or written documentation would be great. Video documentation would be acceptable. I'd even be grateful for a simple list of steps take to accomplish this pretty basic task.
Thanks!
the data type of a reference link in wix is just text, and it should be the _id of the referenced object. if your users submit forms, then the '_owner' field in the object you are inserting into the table is the same as the logged in member's id ( '_id').
here's an example of adding a reference field into wix table through a data hook:
export function Students_beforeInsert(item, context) { console.log("INSIDE STUDENTS INSERT HOOK") console.log(item) // formatting the student's name item.title = toCamelCase(item.title); // adding the reference link of the parent (member who is submitting form data) to the 'Students' table item.linkedParent = item._owner return item }
Thanks. No data to upload, both the primary dataset and the secondary already contain all the data. I still have not implemented this. Perhaps you could tell me in terms of MY datasets and fields exactly what I need to do?
Primary dataset "CARS" has an email address that I use in wix code to look in the secondary dataset "PEOPLE" to retrieve the name, city, state, zip and phone number of the "owner" of the car when listing the cars or a specific car by dash#ID. The car database as currently exists does NOT have that email defined as a reference field. Therefore I cannot query the CARS dataset for those that exist in a given state, which was my original intent as posted in August 2019. I simply gave up.
Hi @cwvega76, I was sure that by this time you might have found the solution, but as stated in the solution above, if you follow that technique, you can automatically set the right value based on Id received from primary dataset, by using just codes.
Else, the other way around is to add a dropdown element and set it to the reference field, select the option manually before you upload the data ,as understood by you.
This request was posted in 2019!. But Yes, I figured this out a year ago on a different dataset. The downside is that, unlike Oracle SQL the referenced value is not automatic! As you state, you then have to use the content manager and select the right value from the preselected option. In the case cited in my post, I have 2480 cars. I would have to manually select the right state for each record. This is not a solution!
Let me demonstrate :
We have the following datasets:
dataset1 : Primary Collection
dataset2 : Secondary Collection (connected to "Primary Collection" through reference field)
Steps:
1) get the _id field value of dataset1 using dataset1.getCurrentItem(); and store it in say, id
2) set the value of dataset2 reference field using $w("#dataset2").setFieldValue("reference's field key", id);
Done!
After the execution of the program, The reference field in dataset2 collection will have a pre-selected option based on the id value received from dataset1!
does anyone have or know of a working wix video on how a reference is linked to a primary field?
I know its been a while, but could you shed some light on my issue? I too used to work for Oracle but just don't get how to set this up:
I have two datasets... Members and Cars. The members primary field is email_address. Members OWN cars. The cars primary field is dash# but there is an email address that I use to link them together but email is NOT reference field. Using wix code I want to be able to query the Cars database for cars located in a given state. The state field is in the Members dataset. I have read the docs about songs and artists but I can't seem to make the correlation? Common sense tells me I should be able to look in the database manager at the email in the Cars dataset and define it as a reference field to the Members dataset but no such option is there when you look at field type??? I just don't get it?
Please explain and give me the steps to do this.
I have an issue similar to the original post. I have a page with a list of child elements and a "create child" button. I use a dataset of the child collection filtered by the parent to display items in the list. On the create child page, I am using a form with a child dataset to create a new record in a child table. In code, set the reference column value of the dataset to the parent, but when I look at the collection I see a message in a red bubble saying "Reference is broken". What's even more odd is that when I go back to the page with the child list, the new element is there! Why does the collection say I have an invalid reference? Am I doing something wrong or is this a known issue? Is it legal to use a dataset to add a row with a reference field, or should I use a different technique?
Thanks in advance,
Darryl
@calvin, @avillarg in your form use a dropdown element. Connect the dropdown value to a reference field and the options list will be filled with the list of available reference items.
Hi is this possible yet?
Hi Brian
I have exactly the same question as you. I do not what to do with this obvious requirement!
Have you have any news in this last months?
Arturo
Hay Brain,
Had a very similar question just a day ago - https://www.wix.com/code/home/forum/questions-answers/dropdown-value-connected-to-database-reference-field
It can be done using coding, reading the second collection (the referenced collection) using code, creating options, setting as the value the _id from the referenced collection and as the label the title (or primary field) from the referenced collection. From this point, things should be working as the dropdown will display the labels while storing the values - which are the _id of the referenced collection.
Brian, I have not tried it out myself, but after carefully reading your question a couple of times over, I wonder if you have tried to set up a form with a dropdown box connected to the reference field. This would then display a list of employers. Before the 'reference field' update, this would simply write a text or num value to the row, thereby de-normalizing the DB. Looking at the examples, I understand that this has now resolved and a foreign key is written instead. Would this solve your problem?
Good luck.
PS I do not know what you are trying to achieve exactly, but do bear in mind that Wix Code´s db is not a relational DB, but a 'no sql db' . It does not support traditional row locking, transactions over more than 1 collection (table, as in master/detail in on order or invoice) or comit/rollback.
So is this not possible with Wix Code?
When I posted my initial question, I expected to get a quick answer because the question is so simple and so basic to any database. Connecting child records to a parent record is critical for an awful lot of what we do with databases.
Wix has reference fields but it seems the implementation is just in its early stage. Not being able to connect a child record with a parent record in a custom database form is so important that it Wix cannot support this then I will need to find another database.
There are millions of examples of this requirement:
* Adding multiple addresses to a company
* Adding family members to an individual
* Adding lines to an invoice
* Adding students to a class
The question that I have not found an answer for -- and I have looked carefully now for a couple of days -- is how to do this. If I have a collection (say students) and I want to add records to this collection but make them part of a parent collection (say a class), how do I indicate which class the students belong to when I add them in my "add student" form?
Andreas, thank you very much for your reply.
I had followed the information on reference fields to create the reference fields between collections. I think I have that set up correctly.
What I do not understand and cannot find any information about is how to create a form that uses a collection linked to another collection.
For example, suppose I have two collections:
(1) Employers: Employer Name, Address, Email, etc.
(2) Employer Contacts: Employer Name, Contact Name, Job Title, Phone, Email, Notes
I want to set up Employer Name in Employer Contacts as a reference field to Employer Name in Employers. I think I have that set up correctly.
Next, I want to create a form so someone can enter a new employer contact:
(1) I create a new page called Add Employer Contact
(2) I add a data collection for Employer Contacts
(3) I create input fields for the Contact Name, Job Title, Phone, Email, Notes
How do I set up a field so the user can choose which employer they are adding a contact for?
I tried adding a second data collection for Employers to see if that would work. I thought maybe I would add a dropdown list for the employer names so they could choose but I could not seem to get that to work.
So there is the heart of my question. On this sample page we are discussing:
(1) Do I add a second data collection for the Employers table?
(2) How do I add a form field so they will be able to choose the Employer they will add a new contact to?
Thank you very much for your help!
Hey
You can use referece fields between several data collections.
Choose Primary Field and then you can connect them together. There is a video class on this topic coming up in days on wixshow.com.