Problems with reference fields

[Emiel]

Hi @teodor,

Thanks for this preview/tutorial. I can’t get the value of one of my reference fields set for some reason.

I got this customers table, which has a customer_id (increments/key) field.

In my orders table I got a customer reference field:

In my action file I am creating a customer record in the customers database table and want to store the output’s identity field in the orders table’s customer reference field, but for some reason the field stays empty (null). What am I doing wrong here?

BTW: When (re)naming the orders table’s customer reference field to customer_id (same name as the key/increments field of the customers table, like you did in your preview post here), the database manager gets tangled up: The field can’t be changed anymore then, the customers table customer_id value is changed to a reference type field then and applying database changes throws errors then. I had this problem with several reference fields yet, so I think it might be a bug.

Hope you can help, thanks in advance!

[Teodor]

Hello,

@Emiel

In my action file I am creating a customer record in the customers database table and want to store the output’s identity field in the orders table’s customer reference field, but for some reason the field stays empty (null). What am I doing wrong here?

Are you sure the binding you are using there actually returns a value?
Create a setvalue step, use the same expression there, enable the setvalue output and do the insert.
In your browser check if the setvalue step returns a value (only the last 3 screenshots of this tutorial):

Debugging Server Connect Errors

You can easily debug issues related to server-side processing such as - form not sending email, record not inserting or updating, query not showing data etc. The first thing you need to do is to open the server connect settings. They are located in the Workflows Panel > Server Actions: [Screenshot 2021-09-27 at 10.02.35] And enable the Debug Mode: [Screenshot 2021-09-27 at 10.03.08] Click Save: [Screenshot 2021-09-27 at 10.03.26] In your browser, open the Developer tools. If you don’t k…

@Emiel

BTW: When (re)naming the orders table’s customer reference field to customer_id (same name as the key/increments field of the customers table, like you did in your preview post here), the database manager gets tangled up: The field can’t be changed anymore then, the customers table customer_id value is changed to a reference type field then and applying database changes throws errors then. I had this problem with several reference fields yet, so I think it might be a bug.

I am not sure i understand the issue explained here.
Do you mean that after you create a reference field and then you try to rename it, you can't rename it?

[Emiel]

Hi Teodor and thanks for your reply!

@Teodor

Are you sure the binding you are using there actually returns a value?
Create a setvalue step, use the same expression there, enable the setvalue output and do the insert.
In your browser check if the setvalue step returns a value (only the last 3 screenshots of this tutorial):

I did this and the value appears to be empty indeed. I don't understand how that is possible, because the record I am getting the identity field from is successfully created in the insert_customer step:

Do you have any clue?

@Teodor

I am not sure i understand the issue explained here.
Do you mean that after you create a reference field and then you try to rename it, you can’t rename it?

No, that's not what I meant. The problem occurs when a reference field in table B is named or renamed a non-unique name (the same name as the foreign field in table A). If it's still not clear or you can't reproduce this, I can should a video or screenshots of this later.

[Teodor]

@Emiel

I did this and the value appears to be empty indeed. I don’t understand how that is possible, because the record I am getting the identity field from is successfully created in the insert_customer step:

Do you have any clue?

What database are you using exactly, on what server model?

@Emiel

The problem occurs when a reference field in table B is named or renamed a non-unique name (the same name as the foreign field in table A).

So it is not related to renaming, or is it? Sorry i don't understand this issue.

[Emiel]

@Teodor

What database are you using exactly, on what server model?

PostgreSQL on Docker Local and Docker Remote on DO.

@Teodor

So it is not related to renaming, or is it? Sorry i don’t understand this issue.

Happens on new reference field creation with same name as foreign field and when an existing field is renamed to the same name as the foreign field.

[Teodor]

@Emiel

Happens on new reference field creation with same name as foreign field and when an existing field is renamed to the same name as the foreign field.

What happens then exactly? What if the two fields have the same name?

@Emiel

@Teodor

What database are you using exactly, on what server model?

PostgreSQL on Docker Local and Docker Remote on DO.

@patrick will check this.

[Emiel]

@Teodor

What happens then exactly? What if the two fields have the same name?

When changing the name of reference field order_lines to order_record_id, like this:

(or on creation of a new reference field with that same name as the foreign order_record_id name)

Then this happens after applying the database changes:

The order_record_id field in order_records becomes a reference field too. When the order_record_id field is clicked from the orders table, the order_records_id field from the order_records gets selected and the field can't be modified anymore.

I do understand this is kind of confusing, but I wouldn't know how to explain this better..

[Emiel]

@Teodor

@patrick will check this.

Okay great, thanks. It's the default db btw, but these are the project's database settings:

[Teodor]

@Emiel

The order_record_id field in order_records becomes a reference field too. When the order_record_id field is clicked from the orders table, the order_records_id field from the order_records gets selected and the field can’t be modified anymore.

I do understand this is kind of confusing, but I wouldn’t know how to explain this better…

I see - i think it is only an UI issue, but @George will check this.

[Teodor]

The problem with the reference fields naming will be fixed in the next update.

[Emiel]

Okay great Teodor, any chance I can get a patch for this before the next update?

[Teodor]

No, we don’t provide ‘patches’ like that.
Just wait a couple of days to get the next beta.

[Emiel]

Okay I will have to be patient then… Thanks!

[Emiel]

While the renaming issue was solved in the latest update fortunately (thanks for that! ), there are remaining issues when editing reference fields, more specifically when removing or changing them.
I tried changing On delete and On update properties for a reference field, which returns this error while applying the database changes:

Here’s the respecitve migration file’s contents:

exports.up = function(knex) {
  return knex.schema
    .table('order_records', function (table) {
      table.dropForeign('product');
      table.foreign('product').references('product_id').inTable('products').onUpdate('CASCADE').onDelete('CASCADE');
    })
};

exports.down = function(knex) {
  return knex.schema
    .table('order_records', function (table) {
      table.dropForeign('product');
      table.foreign('product').references('product_id').inTable('products');
    })
};

Also on deletion of the same product reference field, I get an error:

[Emiel]

@patrick, @Teodor, @George, I want to praise you for the amazing improvements that are included in Wappler 3.0.0 and up regarding the database manager and support for postgresql databases with reference fields!

All works like a charm now!!