Solved

Auto popluate a field in another Database

Posted on 2006-10-25
8
292 Views
Last Modified: 2010-04-27
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
Comment
Question by:scopeortho
  • 4
  • 4
8 Comments
 
LVL 28

Expert Comment

by:lesouef
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...
0
 

Author Comment

by:scopeortho
ID: 17812770
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
 
LVL 28

Expert Comment

by:lesouef
ID: 17815101
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
 
LVL 28

Accepted Solution

by:
lesouef earned 50 total points
ID: 17815663
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:scopeortho
ID: 17815794
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
 
LVL 28

Expert Comment

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

Author Comment

by:scopeortho
ID: 17902027
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
 

Author Comment

by:scopeortho
ID: 17915034
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now