Modify field based on layout name

[acantho]

Here's the situation.

I have a database that basically runs monthly.

Each month has a certain bunch of layouts created all starting with the month "Jan05" "Feb05" etc. For example we might have a layout called "Jan05 statement". All the fields on these layouts are also labelled "Jan05" etc. so on the "Jan05 Statement" layout there might be a "Jan05 Balance" field.

What I'm trying to do is write a script that modifies a field where that field is different each month but always has a similar 5 digit code to start without having to re-write the script every month (cause it's a long long script as it is). To add to the mix, the field is a repeating field and I need to add to each repetition seperately.

Is this even possible?

I've been trying this thing for weeks now and haven't been able to come up with anything in filemaker that would allow it to happen.

[acantho]

So nobody has any ideas on this?

I mean it would be really cool to do but I haven't figured it out yet.

If somebody knows of a way to reference text from a field, and use that text to identify an actual field to use in a calculation then I might be able to get it to work

ex.

FieldC= Left(FieldA, 3) & Right(FieldB,3)

Then the calculation is something like SetField(textfromFieldC) = blah blah blah

I mean that would be cool but I need to know how to make a calculation use the "textfromFieldC" and recognise it as an actual field name for that to work.

Someone out there must be a master and be able to figure this out!

[Ender]

The getfield() function can do part of what you want: access field data based on a field name.

But the whole problem could be avoided if you changed to a relational structure. Have a file for Billing Period (one record per month-year) and a file for Billing Items. Each Biling Period would have many Billing Items. This would likely reduce the number of fields you have and reduce the amount of scripting required.

[acantho]

Well the only problem with this is the reason it's monthly is that it corresponds to clients and we have thousands of clients.

So it's their monthly information and if I was making a new database for each month, then it would be about the same work as I would then have to create and modify fields calculating totals cause our DB goes back many many years!

Basically we create a new DB every 1-2 years as any information more then 2 years old isn't quite as requested or accessed. But we get inquiries into information that's up to 2 years old all the time and we have to be able to access that extremely quick over multiple computers. I just don't see how having multiple databases would make that as easy considering just how many we have open already. Adding another 12 or 24 databases per each database we have already seems like a lot!

We currently have a database set up kinda like what you just described and if someone asks a question relating to that database about data collected in a previous month it can take as long as 10 minutes to find an answer. That just isn't an acceptable return time for customer service.

and as far as I know the getfield() function would still want a field in between the parenthesis. I'm really looking for a way to get filemaker to use information from inside a field as the name of the field it should modify, enter, etc. Cause if I could find that I might be able to get this to work.

Kinda like a texttofield() function LOL

Does it have something like that??

Thanks for the suggestion though. Much appreciated.

[-Queue-]

I'm confused why you would need a different layout for each month's report. You should be able to use a single, dynamic layout, unless the reports are formatted differently each month.

And Ender wasn't suggesting a new db for each month, but one related record per month-year. You would only need one extra db.

[Ender]

acantho,

It sounds like you haven't really done much relational design yet. This is a great time to learn about it, as this should solve your structure issues, making maintenance much easier, and access speeds faster.

I recommend reading about relationships, portals, and relational design. You can find some basic info in the FileMaker user manuals, or go to 3rd party books about FileMaker Pro (version 5 is very similar to version 5.5 and 6.)

[acantho]

Yes each month is formatted differently... very differently at times. This is a database that has been developed over many many years.

The database is basically clients and each month gets a new layout with specific fields for that month

for ex. Jan05 Starting Balance for the January 2005 statement called Jan05 State

Feb05 Starting Balance for the February 2005 statement called Feb05 State

Mar05 Starting Balance for March 2005 statement called Mar05 State

And I'm not sure how having the other database with one related record would solve this problem. Maybe you could explain it a little better?

I know I'm terrible at explaining problems which is why I try and add in examples each time. But it's nice to actually get some feedback. Some of the other forums I've been to don't seem to have any knowledgeable people on them You guys are great!

[-Queue-]

With a related file, you wouldn't need fields in the main file for each month's starting balance, etc., merely a relationship to pull the correct related fields and sum them, etc. Most likely you would select the month and year from value lists in global fields and use a relationship from a field that concatenates the two to a similar field in the related file. Any calculations performed with the related fields would then only be applicable to that given month-year combination. So you would only need one layout to display that information, regardless of the month or year.

I second Ender's suggestion about reading more about relational design. You'll make your system much more streamlined and dynamic (not to mention require less upkeep) in the long run, if you implement such a structure in your solution.

[acantho]

I'm still confused as to how this would work. Maybe it's just because I know the databases very well and you both seem to be picturing something completely different.

Cause from what I read above... the information that would be contained in the fields Jan05 Starting Balance, Feb05 Starting Balance, Mar05 Starting Balance is being stored elsewhere and is being referenced by the main database (which I'm guessing would have to just be a referencing database for viewing since it changes monthly?).

I kinda understand your reasons for it cause then I'd be able to do what I want to do above. But at the same time it doesn't seem to solve the problem as it seems to add work when I'm trying to reduce the amount of work needed.

The monthly layouts have similar designs but the information contained in the fields changes each month and fields get added and taken away constantly.

Maybe my example of Jan05 Starting Balance was a bad example?

Hmmmmmm

[acantho]

I've been looking at your ideas and no matter which direction I go using your ideas it always comes out as being more work.

Though it makes it possible to do what I'm "wishing" to do, in the end it makes no sense to go ahead in that direction as the only reason I'm wishing to do what I'm doing is so that I have a little bit less work.

[Ender]

Hmm...

Your way: Add fields and scripts each month.

Our way: Add a record each month.

Your way seems like more work.

[bikergeek]

Acantho, basically the argument they're making is that any database that requires STRUCTURAL changes to allow new data is not well designed. By structural changes, I mean new fields for new data. If the structure is well designed, then new data can fit into the existing structure and be quickly and easily available.

That being said, a workaround I came up with for referencing a dynamically identified field in a Set Field script step is as follows:

Create a text field called targetField. It can be a calculation such as

Let ( mo = Month (Get(CurrentDate)) ; Case( mo=1;"Jan"; ... ; mo=12;"Dec") & Right (Year(Get(CurrentDate));2))

(Results in "Jan05" , "Feb05" etc, whichever is current.)

Then your script has the following:

Go to Field [staticField]

Loop

Go to Next Field

Exit Loop If [ Get(ActiveFieldName)=targetField ]

End Loop

Set Field [ ; "Data to be placed in field." ]

Note that you are using Set Field without specifying the target field, because you've arrived at the target field using Go To Next Field and checked against the contents of staticField to make sure it is the correct one.

[bikergeek]

Oops. The Exit Loop should read:

Exit Loop If [ Get(ActiveFieldName)=targetField or Get(ActiveFieldName)="staticField" ]

That gives your script an out after one full loop in case no field has been created for the current month.

[acantho]

I understand their reasoning... The problem is how would I distinguish data that's for Jan from data that's for Feb?

cause right now each record is a client and the data goes via month to month for each client.

I'm talking about 4000 to 10000 clients.

This all sounds like an interesting idea and maybe I'm just not seeing it the way you've described it. And yes I've been thinking about it ever since we started talking about it. LOL

I had filemaker on the brain all last night!

[acantho]

If I'm reading this correctly...

Wouldn't your method require 4000 to 10000 records each month then?

I'm in filemaker 5.5 and with this many records alone, some of the calculations can take a long time to process. I can only imagine how long it would take if it increased by that much each month.

EEK!

Please tell me this isn't the case.

[Ender]

Yes, you would have a Billing (or Invoice) record for each month-year, and then one or more Line Items for each Billing record. If you bill 4000-10,000 clients per month, then you'll have that many Billing records plus Line Items for each.

In my experience, the number of records does not degrade performance until you get around 300,000 records. If you have slow calcs at 10,000 records, then the calcs are probably unstored, either because they reference related fields or aggregate functions, or because you have set them to be unstored. There are ways to turn unstored calcs into stored values, but we would need to know specifics to advise. Values that are stored can be indexed, making finds and sort operations on those fields very fast.

Generating Billing records each month can be scripted, depending on how you bill.

[acantho]

This is an interesting idea.

I don't know if it will work in our case but I'll see if I can get things in some kind of fashion to test it. I don't foresee us implementing a new system like this anytime soon simply cause we don't have the time to completely rebuild all the databases.

So does anybody know how to make a texttofield() calculation anyways?

[acantho]

Oh I like that Get(ActiveFieldName) script...

That seems like it would actually work for what I'm doing. Cause I can retrieve the layout name and thus populate the name of a field off of it...

GENIUS!

Basically goes through the entire layout to find the right field and then changes it. Clever clever clever!