Adding record with a reference field

Ask the community
,
1

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!

4 Likes
2

Hey
You can use referece fields between several data collections.

1676×722 133 KB

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.

3

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!

1 Like
4

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?

5

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.

6

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.

7

Hello, Brian. I’m facing the same outrage after almost 3 months trying to understand the WIX proposal and this “WIX database” to have a simple website running. From the outset, I assumed that this could be done in one or two months, due to the extreme ease of creating pages, forms, virtual stores etc. But now, I think I would have started with java and MySQL or another database. I am a systems analyst and, like you, I have many years of IT experience (maybe 30. I am 61 years old). And the main thing in any system we all know is a CRUD. So despite the easy way to start a website in WIX, I can not spend much time and find out that WIX does not have the most basic feature. I think WIX should invest heavily in these issues or it will only be a tool for beginner “bloggers”. Kind regards.

8

Toward the bottom of the WiX Help Center page, CMS: About Displaying Content from Multiple Database Collections Using Reference Fields | Help Center | Wix.com it states:

"Connecting Dropdown Elements to Reference Fields

You can connect a dropdown list element to a reference field. This means that the items in the dropdown list automatically come from the primary field in the referenced collection. If you do this on an input form, the value selected by your visitor gets saved to the reference field in your collection. "

Is this a true statement? Because it’s exactly what I need to do, but any Reference Field I try to connect to a dropdown list element is grayed out.

9

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

10

Hi is this possible yet?

11

@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.

12

@Shay Thank you for your answer. I’ll try to do this.

13

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

14

The solution was simple: when adding a new row to the child table, use the _id of the parent table, not the Main field.

15

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.

16

does anyone have or know of a working wix video on how a reference is linked to a primary field?

17

Hi did you create a how to guide video on reference fields?

18

@darrylchallenger It worked for me! Thanks a lot Darryl!

19

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 !

20

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!