1:M link in ACS FK (relationship)

[chan2111]

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.

[prasannakumarmarru]

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!

[chan2111]

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?

[prasannakumarmarru]

May I know what datatype is that? I don't remember the error, will try again and let you know.

[chan2111]

String

[prasannakumarmarru]

Hi Chan,

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

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-

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

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!

[chan2111]

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

[prasannakumarmarru]

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

[chan2111]

Please check if you are able to open the record in the client data section.