Find and Merge scripts' issue

[ShamanTonberry]

Hello everyone,

As I am still relatively new to FileMaker, especially, scripts, I would like to ask for your help.

I am currently in the process of preparing a historical database of officials delegated to maintain order in private estates. The existence of both the officials and the estates needs to be confirmed by historical sources. So the 3 tables are: Sources, Jito (the officials) and Jito-shiki (the jurisdictions in estates). One source can mention multiple officials and estates; an official may appear in many sources and hold jurisdiction in numerous estates; lastly, a given estate may appear in multiple sources and may have been under the jurisdiction of numerous officials during that time period. While I initially tried a many-to-many-to-many approach, it turned out too clunky and I settled for using a single table (labelled 'Joint' for the time being) that would record all instances a source mentioned a jito in relation to a jito-shiki. You can find the relationship graph below.

In order to avoid creating numerous half-empty records, I limited the possibility of adding records to the Source layout only (since every official and jurisdiction need to be validated by sources) and designed an "Add record" button with the following script (mainly for reference):

Set Variable [ $$Source_id; Value:Sources::Source_id ]
Go to Layout [ “Joint” (Joint) ]
New Record/Request
Set Field [ Joint::Source_id; $$Source_id ]
Go to Layout [ “Jito_Individual” (Jito) ]
New Record/Request
Set Variable [ $$Jito_id; Value:Jito::Jito_id ]
Go to Layout [ “Joint” (Joint) ]
Set Field [ Joint::Jito_id; $$Jito_id ]
Go to Layout [ “Jito_shiki_Individual” (Jito_shiki) ]
New Record/Request
Set Variable [ $$Jito_shiki_id; Value:Jito_shiki::Jito_shiki_id ]
Go to Layout [ “Joint” (Joint) ]
Set Field [ Joint::Jito_shiki_id; $$Jito_shiki_id ]
Go to Layout [ original layout ]

For each record added this way, I type in the Source details in the Source layout and the basic information about Jito and Jito-shiki in the portal tool. Now here is the main issue - every once in a while the source will mention Jito and Jito-shiki I already have in the database. If that happens, I would like to be able to 'merge' those records (I'm thinking changing, say, the Jito_id in the new Joint record and deleting the resulting ghost record from the Jito table). The way I approached was:

1) Setting up a Script Trigger on Validation in the field that would inform me, if the name I have typed in is already figured in the database. I tried to use different search functions and criteria, but never got the desired result - i.e., a list of all the officials/estates that would have the same name, excluding the one I've just typed in.

2) In search results, add a button to every record that would 'merge' those records - I tried along the lines of overwriting the newly added record's ID in the Joint table to that of the record I want to merge it with, followed by deleting the now unnecessary ghost record in its original layout. I tired to base it on establishing variables, but the resulting mess always seemed to fall apart.

Would you happen to have any suggestions/ideas on how I could approach this issue in a way that would make it, well work?

Thank you in advance for your input.

 

[ShamanTonberry]

Hi everyone,

It's me again. I managed to solve part of my issue, i.e. I put together a script that, on validation, searches the field for other instances of the same value (in this case, a name) and displays them in a separate list sans the record I just typed in:

Set Error Capture [ On ]
Set Variable [ $$Jito_id; Value:Jito::Jito_id ]
Set Variable [ $$Jito_search; Value:Jito::Full_name ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Jito::Full_name: “==$$Jito_search” ]
[ Restore ]
If [ Get ( FoundCount ) = 1 ]
Show All Records
Go to Record/Request/Page
[ Last ]
Else If [ Get ( LastError ) = 0 ]
Show Custom Dialog [ Title: "Duplicate item"; Message: "People with that name are already in the database. Please choose a record
to merge"; Default Button: “OK”, Commit: “Yes” ]
Go to Layout [ “Jito_List_proxy” (Jito) ]
Constrain Found Set [ Specified Find Requests: Find Records; Criteria: Jito::Full_name: “==$$Jito_search”
Omit Records; Criteria: Jito::Jito_id: “==$$Jito_id” ]
[ Restore ]
Else If [ Get ( LastError ) = 400 ]
Show Custom Dialog [ Title: "Error 400"; Message: "So, apparently this DID happen and you need to contact the person in charge of
this database"; Default Button: “OK”, Commit: “Yes” ]
Else If [ Get ( LastError ) = 401 ]
Show All Records
Go to Record/Request/Page
[ Last ]
End If

Now, I've been trying to do a merge script that would run after clicking the button for the selected record, but it doesn't seem to run properly. Some investigation showed that after

Go to Related Record [From table: "Jito"; Using layout: "Jito_Individual" (Jito)]

Set Variable [ $$Jito_proxy; Value:Jito::Jito_id ]

The Variable became 0. Does anyone why this issue occurred and how to fix it? I've been battling this for months, I could really use some help...

Thanks in advance.

[comment]

I am afraid I find this very difficult to follow.

Do you want to allow, under some circumstances, a duplicate name? If so, when a user enters a name that already exists, they should be presented with a choice to either select from existing records or create a new one. Otherwise the existing record should be used (or perhaps you would want to warn them about that and give them the chance to edit the name).

In order to show the existing names, you need an auxiliary relationship based on matching names. Then you can use a portal or a card window to show the related records for the user to select from. Or you could use a find, as you seem to be doing now.

Once they have selected an existing record, you need to grab its ID and use it to populate the foreign key field in the join table. This could be all done in the same script, you just need to pause for the user to make their selection. In any case, the action I would expect to see following the user selection is something like:

# GRAB THE SELECTED RECORD'S ID
Set Variable [ $jitoID; Jito::JitoID ]
# RETURN TO THE JOIN TABLE'S RECORD IN PROGRESS
Go to Layout [ Join Table ]
# MAKE THIS RECORD RELATED TO THE SELECTED ONE
Set Field [ JoinTable::JitoID; $jitoID ]

 

Note that this is using a script $variable, not a global $$variable. Global variables persist until the end of the session, consuming memory and potentially interfering with susequent calls of the same script.

 

[ShamanTonberry]

Thank you, that is exactly what I needed to solve my issue. I do have one follow-up question, though.

After merging records (I decided to make two separate scripts since they were easier for me to set up and follow) I find my Jito tables limited to the results of the find set from the Find function. Since it is both tedious and confusing to the end user to be forced to click "Show all records" every time, at which point of the script should I add "Show all records" to fix it? Or is there a better way around it?

Thanks in advance.

 

Edited July 8, 2020 by ShamanTonberry
Typo

[comment]

If you open a new window before performing the find in the Jito table, and close it to return to the join table's layout, then your found sets in the original window will remain intact. Are you still using version 15? In version 16 and higher, you can open a card window, which would be very convenient for the described situation. 

Alternatively, you could make your script do Show All Records[] just before returning to the layout of the join table. But that means that the user will see all records when they navigate to the Jito table afterwards - which may not be what they expect to see.

 

[ShamanTonberry]

Unfortunately I am still using FileMaker 15 and I have no liberty to upgrade it personally. The other solution is just fine, though, since the users will actually expect to see the full jito list after merging records and anything else they would most likely find confusing.

Once again, thank you for all your help! I couldn't have completed this project without it