I have a GlobalField

[sullyman]

I have a GlobalField where users type in a project number and then all associated project records are returned.

Example A user types in 99112 in the globalfield and this returns all Project 99112 records. The Project_Id assosciated with this Project No. is 001.

I now want users to be able to create records.

When a user creates a record i wish to have the project_id number inputted automatically. So if a new user creates a record, i'd like the project_id field to check what project number was inputted in the Globalfield and return the correct project_id value in the field.

Is this possible? Trying to work it out here with Set Fields etc. but can't get it going

[Inky Phil]

Hi Sully

your explanation is a little ambiguous in that you do not make it clear whether or not the id is still in the global field, or in the case of a new user has been stored elsewhere by now.

Either way it is quite easy to get what you want. You simply make the project id field an auto entry field by calculation. In the calculation enter the name of the field that has the id that you want to capture and every time a record is created it will auto enter that detail.

HTH

Phil

[sullyman]

Hi Phil,

I am actually connecting to an SQL Table. I have a table in SQL with two fields i.e. Project_No. & Project_ID

One record contains the values Project_No - 19112 & Project_ID = 001

I've managed to create a field which is a globalfield where the user inputs the Project_No 19112 and then a find script returns all fields for that project.

The problem i am having is that i wish to autoenter the Project_ID field and not the Project no which is held in the globalfield.

Any help appreciated.

your explanation is a little ambiguous in that you do not make it clear whether or not the id is still in the global field, or in the case of a new user has been stored elsewhere by now.

Either way it is quite easy to get what you want. You simply make the project id field an auto entry field by calculation. In the calculation enter the name of the field that has the id that you want to capture and every time a record is created it will auto enter that detail.

HTH

Phil

[Inky Phil]

Sorry Sully

I have no experience with SQL so hopefully someone else will step in here

Good Luck

Regards

Phil

[sullyman]

Thanks Phil

I don't think it is down to SQL though.

All i want is to perform a search on the relevant project number which is inputted and stored in the globalfield and return the associated project_id with it.

Something along the lines of the following thinking (Commands are not right obviously)

Perform Find ProjectNumber::GlobalField

Get Associated Proj_id

Set Field = Proj_id

[mr_vodka]

I have a GlobalField where users type in a project number and then all associated project records are returned.

Example A user types in 99112 in the globalfield and this returns all Project 99112 records. The Project_Id assosciated with this Project No. is 001.

I now want users to be able to create records.

When a user creates a record i wish to have the project_id number inputted automatically. So if a new user creates a record, i'd like the project_id field to check what project number was inputted in the Globalfield and return the correct project_id value in the field.

Are you usng ESS? If so, how have you identified your unique criteria for your join? Your post is not clear. To me it seems as though you want to type in something into a global field, create a new record in your FileMaker table, and have the Project_ID field in this new record populated with the matching record from your SQl table.

However, you state that there are more than 1 record with the same project number that you typed into the global field. So which record_ID do you want?

Please clarify your situation.

[sullyman]

OK perhaps i didn't explain properly

I have two projects for example in a Join Table

PRO_Line_id

19110 Project 1 (This has an fK_Id of 100)

18110 Project 2 (This has a fK_Id of 200)

Users can perform a find for all records in Project 19110 by inputting 19110 in my GlobalField i have setup that then performs a Find and retrieves all records with Project Number 19110.

I have my link/join table setup in SQL and at present, i am able to create new records etc. I also have a Local Project Table which imports the correct Project Number/ID so users can select this from a DropDown list and hence write back to SQL Server.

Let's say for example that a user has typed in 19110 and is looking at records for 19110. Now, the user wants to create a record. In the way i have setup, no problem, the pick the relevant dropdownlist for Project Number/ID and continue on.

I am wondering is there a way that i can get rid of this dropdown as at a future date, there could be 100 projects and a dropdownlist for 100 would not be good.

Here is my thinking. If the user has already inputted 19110 in the globalfield and viewing records for that project 19110. Is there a way that a script and grab a hold of the current Project_ID entry from the current Globalfield inputted and populate my Proj_ID/Project number fields automatically for new records.

Edited February 18, 2008 by Guest

[mr_vodka]

So if you already performed a search for 19110 and it returned a valid set of records, it would mean that each record with 19110 would have an fK_Id of 100 as well correct?

In that case, you should be able to grab the value from those two fields into variables and then execute a SQL statement to create a new record with those values.

[sullyman]

Correct. Yes, but where do i start!

I'm a novice so any help would be appreciated.

[mr_vodka]

Well it seems to me like you could just perform and execute SQL command.

I am still a little unsure of whats going on in your setup but I guess if I were to take a stab at it, you could do the following. I am going to assume that you are using ESS although you havent explained if that is what you are using or not.

If gProjectID is the field that you enter the search criteria in and if it returns a valid set of records from your SQL table, that means that you should be able to get the related YourSQLField_fK_Id either through a SELECT SQL Statement or through ESS.

Something like...

SELECT YourSQLField_fK_Id FROM yourSQLTableName WHERE YourSQLField_Project_ID = gProjectID

Then you can insert a new record with something like:

INSERT INTO "yourSQLTableName" ("YourSQLField_fK_Id", "YourSQLField_Project_ID")

VALUES ($fkID, gProjectID )

I havent checked the syntax so it may not be 100% correct.