Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Auto popluate a field in another Database

Posted on 2006-10-25
Medium Priority
Last Modified: 2010-04-27

I am not strong in DB administration, so I believe this question might be easier than what I think it is.  I need to automate a value on a field when I select a certain field from another database.  First of all, the two database I am talking about have a relationship established but on different Fields than the ones I am working on.  I am not sure if I need to make another relationship with the fields I am trying to manipulate.  In a certain Form I have a drop down list with four options available.  When I choose "CA" which is one of the options available in the database called "Quotes.fp5".  I want it to populate the value "Completed" in the field "Job Status" in another database called "Projects.fp5".  I have an "IF" statement set inside this script.  

If [Office="CA"]
   Insert Text[Select,”Projects::Job Status” = "Completed"]
End If

It does not change the Job Status.  Now, the Field "Job Status" in Project.fp5 is set to Auto-Enter Data "Current".  So I thought the Insert Text option would do the trick.  Do I use the “Set Field” Option to do this?  I have been digging at this for the past 3 hours any help would be greatly appreciated.

Question by:scopeortho
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 28

Expert Comment

ID: 17808026
I can't say if the relationships you have are ok to do that without seeing the file.
let's assume the projects and the quotes are linked OK.
First, are changes enabled for the auto-enter field? ( don't remember if fm6 has this feature as I am now working with v8)
2nd, are they only 1 project for 1 quote?
3rd, the easiest is to make the status a calculated field because the insert command only works if the field is present in the current window at script execution time, is that true now? Always better to use commands which are independant from layouts...

Author Comment

ID: 17812770

Thanks for the reply!

First, are changes enabled for the auto-enter field?
Yes they are.

2nd are they only 1 project for 1 quote?
Well if I understand this question, yes.  When I create a quote per say in the "Quotes.fp5" it will create one project per say in the "Project.fp5".

3rd ....
You are correct the field does not live in the database script I am trying to manipulate (Quotes.fp5) the Field lives in the Projects.fp5 db.  

Can't I create a Lookup field in the Qutes.fp5 (projects::job status) and go about using the insert text option in the script?  But what I want is to make this relationship valid only when I choose "CA" as the office.  I do not want all the records in Quotes.fp5 to have the lookup field in their records, on the records with "CA" as the office (office Field).  Can you set up a conditional relationship as this, where it will make that determiniation if the Field Office = CA create a field with the Lookup of Projects::Job Status.  I don't think that is possible but I am clueless on getting this done!

Always better to use commands which are independant from layouts...
What do you mean by this?  Like I said I am not familiar with FileMaker in fact this is the first time that I have to support it as an Administrator.  This database has been over the years, been patched by different FileMaker Database contractors so some of the fields and scripts have differences.  But, never the less I have been tasked on making this change.  Well after deciphering the relationships and the fields associated with these specific two databases, this is as far as I have gotten.  

LVL 28

Expert Comment

ID: 17815101
to get yr script work, place the remote field in the current layout (quotes::status) and it should work.
using a calculated field would also take the condition into account, but probably less easy if you are not familiar with the app. let me know if I should explain this also

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 28

Accepted Solution

lesouef earned 200 total points
ID: 17815663
again, I types it wrong...
to get yr script work, place the remote field in the current layout (project::status in the quotes layout) and it should work.
using a calculated field would also take the condition into account, but probably less easy if you are not familiar with the app.
the easiest to go to related record, and call another script in projects which will do the job. You can't have a script that does someting in another file, you need to use a script in the current file which calls another one in quotes to do the insert or set field action. but placing the related field projects::status in the quotes layout is easier.
some scripts commands work without the field to be present in the current layout, some others don't. Insert belong to the later category, it behaves exactly like if you would do it by hand. adn the fact that the other window is open does nto change anything, it must be the active layout. This is why you often see layout changes in scripts, even if you don"t need to see them, it's to get them active before doing something to one of their fields.

If you don't succeed, give me a copy of yr file (empty if confidential) and I'll modify it for you. You'll then be able to repeat the mod in yours.

Author Comment

ID: 17815794

I will definetly give this a try and I will let you know how it goes.  I am not sure how to give you an empty copy of the database the information is confidential.  

LVL 28

Expert Comment

ID: 17815896
to make a copy of the files: save both files as empty clones (file/save a copy...)

Author Comment

ID: 17902027

I have not had a chance to try these changes I have been swamped with Networking changes throughout our organization, I will be attempting this tomorrow if time is permitted.


Author Comment

ID: 17915034

I put the relational field into the layout in Quotes.FP5 and my original Script did not work.  But just as you mentioned to make a change you must apply it on that Database.  So I created a Script in Projects.FP5 that would change the Job Status to "Completed".  This script is called in Quotes.FP5 when the office "CA" is Selected.  It works Great!  Thanks for the advice!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question