[Solved] Error Integrity constraint violation - no parent SY

[Torieth]

Hi, I'm new to this forum and to Base and that may be why I'm struggling so much to do anything. I have learned a lot in this forum without having any account. So thank you, guys, for the help already given.

I have a problem. I'm trying to make a form out of a table with a field named "ID_REQUEST" wich is a Primary key, autocomplete BIGINT from another table with all requests so far.

I can't fill in the ID_REQUEST due to it's randomness, but, I have two fields that can. They are REQUEST_NUMBER and REQUEST_ENTRY, they, combined, answer for a unique request. Then I have another problem... A single request may have or not multiple products and there is one resgistry for each product, repeating the combination REQUEST_NUMBER and REQUEST_ENTRY.

I tried the following to select a request from a list box:

Data field is set to: ID_REQUEST
The list box content is obtained by a SQL query like this one: SELECT DSITINCT "REQUEST_NUMBER" || '/' || "REQUEST_ENTRY" FROM "TAB_REQUEST"

When I test this form, it gives me an error called
Integrity constraint violation - no parent SYS_FK_115 table: TAB_REQUEST in statement [INSERT INTO "TAB_PRODUCTION" ( "DATE","ID_COLAB","ID_REQUEST","ID_SETOR","OBS","PRODUCTION") VALUES ( ?,?,?,?,?,?)]

How can I solve this?

[Villeroy]

Your struggling has nothing to do with Base.
Your database table has a foreign key or two which means that you can not put any value into this field unless it has an equivalent value in the primary key of the referenced table.
I guess that you are going to insert a concatenated value
"REQUEST_NUMBER" || '/' || "REQUEST_ENTRY" FROM "TAB_REQUEST"
into your foreign key where just a valid request_number is possible since the request number existst in the TAB_REQUEST but not the concatenated string.
And like so many others you think that a combo box is helpful but a combo box is just a simple text box with some auto-complete functionality.

Convert the combo to a list box and continue here: viewtopic.php?f=13&t=76600&p=349166#p349166

[Torieth]

Thanks for your answer, Villeroy. I'm a bit confused... I'm actually using a list box already as the combo box kept showing zeros in all fields.

Here are the tables I'm using

Production table (the onde I need to make form from)
Name: TAB_PRODUCTION
Fields:

• ID_PRODUCTION
• DATE
• SECTOR
• COLABORATOR
• QUANTITY
• REQUEST
• OBSERVATION

Request table
Name: TAB_REQUEST
Fields:

• ID_REQUEST
• REQUEST_NUMBER
• REQUEST_ENTRY
• Several fields containing details...

Sector table
Name: TAB_SECTOR
Fields:

• ID_SECTOR
• SECTOR
• Several fields containing details...

Colaborator table
Name: TAB_COLABORATOR
Fields:

• ID_COLABORATOR
• COLABORATOR
• Several fields containing details...

The fields REQUEST_NUMBER and REQUEST_ENTRY are together a single Request, I need that whenever I set a REQUEST_NUMBER and REQUEST_ENTRY combination into the form it gives me back always the same ID_REQUEST, no matter wich one. I thought that the "DISTINCT" modifier into the "SELECT" would give me only the first result for each REQUEST_NUMBER and REQUEST_ENTRY combination.

Apparently, I got that wrong and I think I'm getting the forms wrong altogether. I mean, into the list box there is the list I need to be and I can select the combination of REQUEST_NUMBER and REQUEST_ENTRY the way I imagined it. Why does it not track back the ID_REQUEST that gave that combination out?

[Villeroy]

Your current list box selects only one concatenated field. A list box can use 2 fields, a visible one and hidden one to be written.

Following my recipe for a 2-column list box:
Linked field: REQUEST (a foreign key field of this form's row set)
Source Type: SQL (or Query)
Source: SELECT DSITINCT "REQUEST_NUMBER" || '/' || "REQUEST_ENTRY" AS "Visible", ID_REQUEST FROM "TAB_REQUEST" ORDER BY "Visible" (or the name of the query with this statement)
Bound Field: 1 (which is the second field, the primary key of the referred table TAB_REQUEST. The visible field would be 0)

[Torieth]

It worked!

Thank you so much!

Then, it gave me some new doubts... xP

I think the list box will always show only the first field in the query, right? So, I can add as much fields as I wish to order it properly or is there a limit? I mean, look at this table:

Colaborator table
Name: TAB_COLABORATOR
Fields:

• ID_COLABORATOR
• COLABORATOR
• ID_SECTOR
• Several fields containing details...

I need to fill in the colaborator list box with the name of them, now it's like this:

SELECT DISTINCT COLABORATOR , ID_COLABORATOR FROM TAB_COLABORATOR

It's working fine, but, I'd like to order this list by sector and then by colaborator. Is it possible? I tried this:

SELECT DISTINCT COLABORATOR , ID_SECTOR, ID_COLABORATOR FROM TAB_COLABORATOR ORDER BY ID_SECTOR ASC, COLABORATOR ASC

It apparently works, but it shows the first query item in the list box and independently if a choose another item, whenever I change the focus from the list box it will come back to the first item.

Another thing is, I have several details from de request, after selected I'd like to show the request description as a way of confirmation. Can I do that somehow?

[Villeroy]

You don't need to select a column to order by.
SELECT A, B FROM X ORDER BY C works just fine.
or even
SELECT A, B FROM X ORDER BY (SELECT M FROM Y WHERE Y.A = X.Z)
which orders by a another table's field using some related field(s) between the tables.

[Torieth]

Understood!

And about a field showing a query based on another field inserted value? Can I do that?

[Villeroy]

Just compose a query with 2 columns, one visible and one with the other table's primary key to be used as this form's foreign key. The sort order should be ascending by the visible column because the alphabetical order makes it easier to spot things while typing.
The content of the visible field can be anything which helps to lookup the right primary key.

[Torieth]

Villeroy, thank you for all this answers!

I have so many questions. Is there anywhere I can ask several newbie questions? I feel like this thread is not an optimal place to do it...

By field, I meant a form field, not a base field. I'd like to use the data inserted in the fields to filter the next form field query

And, I'd like to know why some list boxes show up the first query item and others don't? I'd like all of them to be void initially.