Jump to content

Portal displaying values not matched in a relationship.


Mountainoak
 Share

This topic is 4328 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)"

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)".

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This topic is 4328 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.