Portal displaying values not matched in a relationship.

[Mountainoak]

My situation is as follows: Table A contains data imported from excel into filemaker. I then use table B through a relationship to add data to table A after which a script will run to export data from A into another table. The problem I can't solve is if the related field has no match in table B no data will be added to table A. I'd like to be able to show the records from table A which have no match in table B based on the relationship in a portal so I can then choose which of this I should add to table B so for future use these records will be related.

So actually I would like to show the records which are not related, based on the related field.

All help appreciated.

[comment]

Could you provide some background to this, rather than the cryptic "Table A, Table B" stuff?

In general, it is easy to show records in another table that are not related to the current record in this table. It's not so easy to show records that are not related to any record in this table.

I suspect the real solution here would be to import the data into "Table B", updating matching records and adding remaining data as new records.

[Mountainoak]

I attached a file. The records with the empty blue fields are the ones I'd like to show in a portal so I'd be able to select the codes I want to add to the "discipline" table. So next time I import an excel file into the import table the blue fields wouldn't be empty.

Portalrelation.zip

[comment]

The records with the empty blue fields are the ones I'd like to show in a portal so I'd be able to select the codes I want to add to the "discipline" table.

Do you need to select the codes you want to add individually - or would it be sufficient to add all the orphans to the "discipline" table, without showing them first (in a portal or by another method)?

[Mountainoak]

I'd prefer to be able to select the individual orphans.

[comment]

I believe the simplest way would be to follow the import into the Import_Excel_CWS table with:

Enter Find Mode[]

Set Field [ Import Discipline::discipline ; "*" ]

Omit Record

Constrain Found Set []

This will retain only newly imported orphans in the found set - and save you the complications of pushing them into a portal.

[Mountainoak]

This indeed seems the easiest solution.

Just to solve the portal question in theory:

I'm still wandering though if there is a way to display the the orphans in a portal through a relationship in an easy way (more specific displaying the omitted records). Or would it need more fields and calculations something like: create a calc field with an if statement( if(empty);1:0). Then set a global field to display the "1" or "0" and use this as reference for the portal to display orphans?

[comment]

It would be a little more complicated than that, I am afraid. A calculation based on the absence of related value cannot be stored - and therefore cannot be used as the matchfield on the "child" side.

See the attached for a possible workaround.

Portalrelation.zip

[Mountainoak]

I thought I'd just add a field with a drop down list displaying the available disciplines and a scripted button to add the unknown cws code to the discipline table with the appropriate discipline. The scripted button would create a new record in the discipline table and insert the unknown cws code with the selected discipline. The thinking was easy the doing so not. The problem I ran into is that I can't create a drop downlist which isn't linked to the table. Each dropdown list I created will change the imported data because it's linked. Would you be so kind to help me solve this issue as well?

Portalrelation-1.fp7.zip

[comment]

I don't quite see why you need to add a value list and another field, when the orphans are already being displayed - both in the found set, and in a portal. Why not simply make a button to add the clicked orphan?

[Mountainoak]

I don't quite see why you need to add a value list and another field, when the orphans are already being displayed - both in the found set, and in a portal. Why not simply make a button to add the clicked orphan?

Because the orphan code needs to be linked to a discipline in the discipline table. How can I do this without pointing out which discipline belongs to the orphan.

[comment]

the orphan code needs to be linked to a discipline in the discipline table

I am not sure I understand this: the orphans have codes that do not exist in the discipline table; for example, in your file there are 4 records with the code "ANEST" - but there is no discipline with this code, therefore nothing to point at.

If you create a new discipline record with the code "ANEST", these 4 orphans will become automatically related to the new record.

[Mountainoak]

Indeed the "anest" will be added to the discipline table but it will be added to field: "discipline code CWS". In the discipline table there is another field called "discipline". The goal is to specify the value in the field "discipline".

So from scratch i want to display orphans in a portal and click a button which adds this orphan to the discipline table. but before i click this button i'd like to specify the value which should be added to the "discipline" field next to the ""discpline code cws."

e.g. i add "anest" as an orphan into "discpline code cws" and i'll add the value "Integementum, Spier, Skelet, (niet Ortho)" to the "discipline" field. So next time I import new records filemaker knows that “anest" means "Integementum, Spier, Skelet, (niet Ortho)"

[comment]

Something like this?

Portalrelation2.zip

[Mountainoak]

Something like this?

Indeed this is my goal.

The values which should be added to the "description" field in the discipline table are limited to the 18 fields currently in the discipline table. The user should not be able to enter another value than one of these 18 descriptions.That's the reason I wanted to use a value list. So is it possible to select the description value from a value list instead of adding them through a window where one enters the value by typing. I'm sorry for the confusion.

So i thought I'd select a value from a value list displaying the 18 records currently in the description field of the discipline table and then click “add" to start a script which creates a new record in the discpline table and adds the selected description from the value list to the description field and the orphan code value to the code field.

[comment]

If I understand you correctly (big IF), the "ANEST" code should be added to the discipline record #1 (currently having a code of "Orthopw") - so that both "ANEST" and Orthopw" (and perhaps a few more) will mean "Integementum, Spier, Skelet, (niet Ortho)".

[Mountainoak]

should be added to the discipline record #1 (currently having a code of "Orthopw") - so that both "ANEST" and Orthopw" (and perhaps a few more) will mean "Integementum, Spier, Skelet, (niet Ortho).

Yes.

I import an excel file into filemaker which contains a column with codes like "Ortopw". Sometimes this column contains new codes like "anest". So i have to add "anest" to one of the "descriptions" my filemaker solution uses (e.g. "Integementum, Spier, Skelet, (niet Ortho)" ).

It stays unique as "anest" belongs to only ONE of the "descriptions" in this case "Integementum, Spier, Skelet, (niet Ortho)".

So I have to select to which one of these 18 "descriptions" used in my file the new code like "anest" belongs.

should be added to the discipline record #1

Do you mean you'd create a list in the code field, or a repeating field? or would it be better to create a new record.

[comment]

How about this way, then?

CodeLookup.zip

Do you mean you'd create a list in the code field, or a repeating field? or would it be better to create a new record.

If that's all these codes are being used for, I'd settle for a return-separated list in the Codes field. Otherwise you may need to create a table of Codes (a child table of Disciplines).

[Mountainoak]

How about this way, then?

This is what I need. Thanks a lot for your patience and effort.