• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Auto popluate a field in another Database

Experts,

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.

Dennis
0
scopeortho
Asked:
scopeortho
  • 4
  • 4
1 Solution
 
lesouefCommented:
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...
0
 
scopeorthoAuthor Commented:
Lesouef,

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.  

Dennis
0
 
lesouefCommented:
2
to get yr script work, place the remote field in the current layout (quotes::status) and it should work.
3
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
lesouefCommented:
again, I types it wrong...
2
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.
3:
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.
4
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.
0
 
scopeorthoAuthor Commented:
lesouef,

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.  

Dennis
0
 
lesouefCommented:
to make a copy of the files: save both files as empty clones (file/save a copy...)
0
 
scopeorthoAuthor Commented:
lsouef,

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.

Dennis
0
 
scopeorthoAuthor Commented:
Lsouef,

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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now