Highlighted

1:M link in ACS FK (relationship)

chan2111

31-10-2019

There are 2 tables A,B.There is FK in the many side of the table A referring to the PK of the table B. I have a requirement that 1 record in the source table B will have many records in the target table A and a record in table A may have the FK value as null/empty.

The question here is if there is an FK whose value is null/empty in the Table A is there a way we can make the field nullable in Table A in ACS.

Replies

Highlighted

prasannakumarmarru

31-10-2019

Chan,

You cannot use nullable Foreign keys in ACS, I have tested it just now and It is creating NOT NULL fields every time. I think you can use 0 as default value when it is created instead of null. Thanks!

Highlighted

chan2111

01-11-2019

I have tested it too ,but is there anyway to make it empty or nullable was the question. So when you try to insert null values in FK field may i know what is the error you were facing. The record will be inserted but when i try to open that record from recipient schema it says resource unavailable/the links are not correct/contact administrator. Were you getting the same error?

Highlighted

prasannakumarmarru

14-11-2019

Hi Chan,

I created tables test and test2, joining with test(fkey is a foreign key) referencing test2(cid).

1855487_pastedImage_3.png

When pulling test2 data with collection of test data, I am not able to add the element from test table in additional data section and seeing this error-

1855484_pastedImage_0.png

But I am able to pull test data as main resource-

1855485_pastedImage_1.png

But when adding the additional data(test2 - cid), it will not show null records as they are not referencing any records from Test2 table, as fkey is null.

Thanks!

Highlighted

chan2111

15-11-2019

prasanna,

Are you able to open the records in UI, although they get inserted I wasn't able to open the profile record which had a fk null. It was showing an error like check the links / contact system administrator.

Thanks

Highlighted

prasannakumarmarru

18-11-2019

I didn't test on Profiles table, created custom resources, but I was able to see data in workflow UI (open transition).