How To Enter Empty (Null) Into Table Field With Relationship From Data Page

[RichardAukland]

Hi, hope you can help

 

I am getting "data integrity error" when using a dropdown list to enter a value for a field that is in a relationship. Basically I want to give the user the chance to enter nothing, i.e. NULL, i.e. not pick anything from the dropdown list. But this does not seem possible on a data page, even with an additional custom value set to empty. So, how do you default to no value when editing with a dropdown?

 

The same problem doesnt seem to exist in when i enter data directly into the table in bridge. In such circumstances there is no obligation to pick from the dropdown list

 

 

Thanks

Richard

[MayMusic]

You should not be able to create relationship if you have checked "Allow Blank Value" for parent ID in parent table!!! Create a new sample table and see if you can replicate it

[RichardAukland]

Thanks for the suggestion MayMusic,

 

However it is not the primary key field I have a problem with, its the foreign key field. I dont seem to be able to have an empty value in the foreign key field when using a Data Page, only when editing a table directly.

 

I dont understand why nobody else has mentioned this, perhaps I am missing something. I would have thought it was a common thing to do. The only workaround I can see is to have an additional value in the lookup table for "Unknown" 

[MayMusic]

If you do not have autonumber as parent ID in parent table you can have one bank value and let user have blank for parent ID in child table. I just tried and it went through

[RichardAukland]

Thanks MayMusic

 

But if I do what you suggest then when I define the relationship it is described as 'indeterminate' and relational integrity is not available.??

[MayMusic]

That is true in fact you should not be able to leave parent blank since it interferes with referential integrity. So it should a bug in the system that lets you leave it blank in table sheet.

 

You do not want to have orphan file and that is why you are having this option checked.

[RichardAukland]

 Ah OK, thanks MayMusic. 

 

Not sure I would call it a bug though. In SQL Server and many rdbms it is quite a normal scenario  to allow NULLS on fields involved in relationships as a foreign key. In fact, I am very surprised this is not implemented by default in the DataPages of Caspio, and I would say the bug is more likely the other way around - the tables are behaving correctly, and the DataPages incorrectly. But I am not a Caspio expert, just someone from a db/programming background. 

[MayMusic]

I agree we should be able to leave parent ID blank in child table so at table level it is acting correctly but not submission page ..