Jump to content

Recommended Posts

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.



Link to post
Share on other sites
  • 5 weeks later...

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.

Link to post
Share on other sites

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:

Set Variable [ $jitoID; Jito::JitoID ]
Go to Layout [ Join Table ]
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.


Link to post
Share on other sites

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 by ShamanTonberry
Link to post
Share on other sites

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.


Link to post
Share on other sites

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

Link to post
Share on other sites

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
  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

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