Script to pull data from a table

[BrentHedden]

Alright - here is my situation. I've set up a small table that has two columns, one that has a field name, and the 2nd to tell what data to set the field to (usually from a separate main database or a calculation of such). How would I write a script to do the following -

1) find the first field on the page (layout), find the corresponding field name in the above mentioned table, and set the field on the layout to the data (2nd column) in the corresponding table.

2) find the next field on the layout, and do the same thing as #1

3) loop this process until it sets the last field on the page, then stop.

I know there is a way to do this , but haven't been able to get it to correctly fill in the data. Any help or suggestions are greatly appreciated!

[Ugo DI LUCA]

Hi,

not sure I understood where you want to go with this and why...

What is exactly the "table" you're refearing to ?

May be the GetField Function could be done or a loop script with Set Field or Insert Calculated result.

As you see, I'd prefer to have more info...

[BrentHedden]

Ahh yes - I guess I didn't explain that very well. The company I'm with produces medical credetialing forms for physicians. Almost all of these applications ask for the same information. So, we've set up a 'main' database(s) for the docs to type in all thier info. Then, we produce a separate file that has the application's layout(s). We then place fields where you would normally handwrite that info in, then write the script(s) to define those fields to the coresponding info from the main database. Since each application is a bit different, the scripts for each application is different, due to the fields being in diffent places or pages. We also make separate scripts for each page of the application, to make it easier for us and our clients.

We want to do this a bit differently, to speed up the time it takes to script all that, since usually applications sometimes are 15-20 pages long.

This is what we basically thought can be done. We want to set up a table or separate file that has all the possible field names in it (regardless if the individual application needs it or not), and also what to define(set) that field to (from the main databases) once called upon. For each application page, we would make a loop that would find the first field on the layout, look for the same field name on this 'table', then set the field in the application to the corresponding data in this 'table'. It will then look for the next field, and do the same thing untill it reaches the last field in that page, then stop.

Once that is set up, all we would have to do is place these fields on the correct spot on the page, then run this loop script to fill in all the info.

So, there are three things involved here -

1) The master database that has all the doc's info.

2) An individual credentialing application.

3) A table or file that will have all possible fields for the individual credentialing application and what info from the master database to set the individual fields to.

Hopefully that clarifies what I'm trying to do.

[cjaeger]

as i unstand it, you simply want to transpose a table, eg

field1. 15

field2: 20

field3: 40

...

into a filemaker database

field1 field2 field3 ...

15 20 40 ...

is this correct?

[BrentHedden]

Yes, that is about what we need to do. I did find a way to automatically fill all the fields on the page, although it is a bit different that what I described previously. It doesn't rely of a table. I'll put it here if anyone out there might find it useful -

Go to Layout [ Form 1 ]

Freeze Window

Exit Record/Request

Go to Next Field

Set Field [ @StartField, Status (CurrentFieldName) ]

Loop

If [ PatternCount(Status(CurrentFieldName), "~") ]

Set Field [ GetField(

Left(

Status(CurrentFieldName),

Position(Status(CurrentFieldName), "~", 1, 1) - 1

)

& "::" &

Right(

Status(CurrentFieldName)

Length(Status(CurrentField Name)) - Position(Status(CurrentFieldName), "~", 1, 1)

)

)]

End If

Go to Next Field

Exit Loop If [ @StartField = Status(currentFieldName) ]

End Loop

Exit Record/Request

The first line (goto layout) is optional. The @StartField in the 5th and 21st lines is a 'dummy' text field that doesn't need to contain any info, it just needs to be made. The field that are placed on the layout need to be named relationship~Field (e.g. Hospital~Address). Of course, the relationship(s) need to be defined first.

If there is another way of doing basically the same thing, I would be interested in reading about it.

[Ugo DI LUCA]

I don't even know if you need a script for that, asuming you have a relationship.

As you did in your script, the GetField associated with aCase or Choose function would do the rest.

Case (document = X, Get Field (::the field you want), document = Y, Get Field(::the field you want)

or

Coose(documant, Get Field (::the field you want), Get Field (::the field you want).... if you numbered your layouts from 0 to ...

[BrentHedden]

Yes Ugo, you are right that we wouldn't need a script for that. BUT, one thing I didn't mention before -

For every field that is on the application layout(s), we put a copy of it behind itself, change the fill color, made it non-printing, and allowed entry into the field. This way, if the client didn't like what was mapped in that particular field, they could just click on it and change it to whatever they wanted. We noticed really quickly that if the field name on the layout is the exact same as the relation::fieldname, that they would permanetly change info in their master database, when they only wanted to make a temporary change (for printing purposes). We had to make the fields on the layout a different name that what was in the relationship to prevent this from happening. We would then make a long script with a bunch of SetFields in it to map the correct data from the relationships (database) to the layout fields. This scripting is what was taking us so long.

[Ugo DI LUCA]

Hi,

This is a good tip but I wouldn't use it in tis occasion. If you want the info to come from a relationship, but would allow the user to change it without affecting the Main file....I'd use a lookup field instead.

As you're changing the fields, target the lookup to the prior GetField. So after having defined all the GetField calcs, the unique script you'll need would be a relookup. You could defined it to be set upon the Modification time, so whenever one record is changed, a lookup is triggered.

I just hope I understoof the case.

[BrentHedden]

HEY - that's a pretty slick idea. I wasn't aware that that function was there. Ya learn something new everyday, huh? Especially with FileMaker! I'm going to try that, to see how it works.