Jump to content
The site is updated on a beta version. Maintenance tasks are running so search and index may not function at this time. ×

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...
  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

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