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
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
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
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
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
to make a copy of the files: save both files as empty clones (file/save a copy...)
ASKER
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
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
ASKER
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!
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!
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...