How Can I Automatically Create a New Field

[DellV]

I am looking for a function (or some other way) that will automatically create a new field in the FMP database on the first day of the new Month.

[John Caballero]

Just any old field?

There isn't a way (to my knowledge) to automatically create fields in FileMaker.

Now data & records, on the other hand, is an entirely different story. What would this new field be used for?

[Lee Smith]

John is correct as far as I know in trying to do this in Native FileMaker.

About the best I could come up with when trying to do this, was to copy a name of a field from a source (such as a global field), use a script to open Define Fields and then Paste in the Name of the field. If left me with the Define Field box open, and ready to complete as to type of field, etc. You can not access the Calculation box either though the script.

Not sure if there is a plugin out there somewhere, but it was suggested that I could probably do all of using AS. However, I'm not verse in AS, and this did do most of what I wanted.

HTH

Lee

[DellV]

This would be an empty field at the beginning of the month and used to collect hours worked during the month. In Access the code used to do this is: dbsCurrent.Execute"Alter Table[" & DataSource & "]" & "ADD Column " & SelectField & Number;"

[John Caballero]

Ah. You could create a separate table (MonthSum.fp5) with a number field. You would then create a script that creates a new record in MonthSum.fp5 at the beginning of the month and sets the value of the field in the new record with the total hours worked for the previous month. Or you could use a calculated result based on a relationship. There are several ways to go about this, but since I don't know your database structure, it's a bit hard to recommend a way to go.

Lee - any ideas?

Also, if you were in an Access environment and you were adding a new column to your table each month, wouldn't your table eventually have a lot of columns? Designing queries would become a real nightmare eventally!

[DanBrill]

Knowing next to nothing about your problem, it sounds like you are setting up a field that will hold the same kind of information that was in there for the previous month, but now for the new month. This is essentially what repeating fields do. Repeating fields are native to FM, and I've never seen any other database product that uses the concept like this (explicitly that is). Essentially you'd have one field where the 1st repetition was January's data, the 2nd repetion was for Feb., and so on. A field can have up to 1000 repetitions.

However, I'd really urge you _not_ to set it up this way. Repeating fields are really a disaster in most people's opinion. (The only time I use them is if I'm storing graphics.) They are almost always a sign of poor database design.

Suppose you have a database of invoices, and each invoice has a list of items purchased. A bad way to design it would be to have the following fields for each record: InvoiceNumber, Item1, Item2, Item3 (the implicit way you can set up repetitions in any database) -OR- InvoiceNumber, Items[set as repeating] (the explicit way you can do it in FM). What happens if yor invoice has 4 items? You're out of luck. What if you want to search for an item across invoices? You have to search on Item1, then Item2, then Item3, etc. What if you want to export data? More problems with the FM style repeating fields.

What you want to do is structure your database so that the repetitions aren't in multiple fields, but are in 1 field in a related table.

Does this help? It probably isn't the quick answer you were looking for, but will save you hours of frustration down the road.

By the way -- welcome to the Forum.

Dan

[DanBrill]

Ah, you guys type faster than I do. More information posted while I was formulating my grand answer. But I think my guess was right, and that John and I are on the same track.

Dan

[DellV]

The database structure has not been set up; I am in the process of taking an Access database and remaking in the more powerful FileMaker Pro. I am open to your suggestions on how best to accomplish this part of my project.

P.S. Thank you for your quick response and good advice; thanks to all of you John, Lee, and Dan.

------------------

-Dell V

[ESpringer]

DellV,

I'd second John's comments, and now Dan's as well. Imagine, a couple years down the line, having dozens of fields lying about, all for data that plays the same structural role. Hmm.

Also, a related-file solution with a record for every month would allow you to add more dimensions of monthly information very easily. (Every time I've created a related file I have soon realized how many *other* kinds of data deserve fields there!)

Having a related file alo facilitates showing (in a portal) the twelve most recent values, recent at top (for example). To do that with fields, you'd have to adjust your layout(s) every month...

[John Caballero]

What does the Access db do? Another possibilty that might work for you is just to have a summary report that you'd run at the start of each month.

[ESpringer]

Dan,

In general the aversion to repeating fields seems justfied, and certainly it wouldn't serve these purposes well over time; a related table will be much more flexible...

What if you want to search for an item across invoices? You have to search on Item1, then Item2, then Item3, etc.

But one thing about your list of disadvantages doesn't seem accurate: searches performed by typing a find request into the first iteration of a repeated field *do* pull records that have the info on iteration 2 or 3, etc., "indiscriminately"... This could be an advantage (and is for the context in which I still use one heavily).

Of course in a case like the one for this posting, it would probably *not* be an advantage -- if, for example, one wanted to find records that had a certain value in June...

[DellV]

John,

This particular database is 1 of 15 relational databases used to track and forecast manpower. I will use CDML tags to enter and retrieve data on web pages. The entire setup is too complex to describe here; the flow chart alone is monstrous.

--------------

-Dell V

[John Caballero]

OK...probably better to go with a seperate table. My experience with CDML is extremely limited - I'm not sure if you can show a summary type of report via CDML.

OK, next question - will you be summarizing total hours worked per month per user? I'm still shooting pretty blind here because I don't know what the Access db (even just this piece) does. I'm not expecting a super-detailed descritpion. An overview would be fine.

[DellV]

This is just an 18 month projection. Each month is text field ; at the beginning of a new month a new field needs to be added. I may have to resort to adding each new month by hand, but having it done automatically would be ideal.

----------------

-Dell V

[DanBrill]

Yes, true. I was thinking of the more general way of repeating fields (field1, field2, etc.) than the FM way, which does at least fix that problem. Still almost never worth using repeating fields.

Dan

[John Caballero]

OK. So in some other file (let's call it Hours.fp5) you have records that show hours worked per day (in an "nHours" field) and the date ("dDate") they were worked. You'll want to create the follwing text calculation (call it cMonthYear):

Month(dDate) & " " & Year(dDate).

In the MonthSum.fp5 file you'll have an auto-enter creation date field ("dMonth1st").

You'll also have a cMonthYear text calculation:

Case(

Month(dMonth1st)=1, 12 & " " & Year(dMonth1st)-1,

Month(dMonth1st)-1 & " " & Year(dMonth1st))

You'll create a relationship from MonthSum.fp5 to Hours.fp5 that is linked by the cMonthYear calculations (Hours_cMonthYear_to_cMonthYear).

To show the sum the hours worked in the previous month, create a calculated number field ("cHoursSum"):

Sum(Hours_cMonthYear_to_cMonthYear::nHours)

At the beginning of the month a script will be executed (how depends on you) that creates a new record in MonthSum.fp5. Keep in mind that if someone goes in and changes the hours worked for a given month, the sum will change as well. If you need to capture that sum before any changes happen, you could create a standard number field and use the Set Field scritp step to set the value of cHoursSum as soon as the record is created.

[DellV]

Thanks John, I will try that.

--------------

-Dell V