Jump to content

2 related questions re: change out part of a field via script & how info is stored

This topic is 5716 days old. Please don't post here. Open a new topic instead.

Recommended Posts


Background: I am trying to develop a database for faculty contracts & attendance. The dept already has one that has hobbled along since FM4 and has a great deal of issues. The goal is the database I am in charge of developing will replace the current one and will be able to grow etc. Although I have database experience, I am new to Filemaker. (version is 8.02 and using both Mac & PC).

I have several tables. People and Account are 2 of them. I have a relationship from People::acct to Accounts::c_Chartfield (this chartfield is a calculated field). I have data in the account- basically 5 parts that make up the 25 or so character account number (AKA chartfield string). Every year one component of the 5 parts changes to reflect the fiscal year (ie AAD05 will change to AAD06 on June 30).


- I want to be able to change all AAD0X to AAD06 --how would I set up a button so that the end user could perform that?

- When we need to change the year, would it be better to just change the existing records or make new records in the accounts table? ----I want to keep the information that was previously entered for last year

For example, Professor Smith is paid out of 60190-AAD05-29900-blah-Blah for this year. When the fiscal year changes, I don’t want this record to change.

But if Professor Smith has a new contract for 2006 fiscal year, then the user will enter one with the new account number 60190-AAD06-29900-blah- blah.

I hope I am being clear… I am in desperate need of coffee and/or sleep.

Attached: relationship and acct data images for clarification.

Thanks bunches



Link to comment
Share on other sites

It would help us to know what the real-world relationship is between People and Accounts. Are there many People to each Account or many Accounts to each People record?

Link to comment
Share on other sites

Hi Ender-

Thanks for responding... and yes that information would be helpful (not sure why I didn't mention that yesterday). I would say the relationship is many to many but let me expand on that.

There could be many people under the same account. Also each person's contract (per year) will have different accounts. I would like to keep the historical data (ie last year Smith's account was xyz and in 2003 it was abc). Jones could also have been paid out of xyz last year and in 2003 -abc.

I hope that is clarifies my situation

Thanks in advance for the assistance.

Link to comment
Share on other sites

I actually made another table called job (which will hold the job and contract info)...so the relationship goes like this:

1 Person (people table) can have many jobs (usually one at a time though) and each job (or contract) will have 1 account number.

I still would like to maintain the historical records... ie last year the account for Smith's job was abc.... and accounts can be used by many people's jobs.

Thanks in advance

Link to comment
Share on other sites

I'm afraid I don't understand the purpose of your account naming conventions, nor can I read the jpgs - and they won't display for me when clicked. You've said it yourself, "60190-AAD05-29900-blah-Blah" ... well, that's all it means to me as well. And we can't help you with something if we don't have a clearer view to work with. Pretend I'm a new employee and you need to quickly fill me in on your admin process. How would you explain it to someone? Forget what you NEED and tell us how it functions ...

Can you post a file to help us understand the relationships required as well?

LaRetta :wink2:

Link to comment
Share on other sites

I hope that the 3 attachments will provide more clarity. Here an account number consists of 5 different parts (ie 60190-AAD05-29900-0101-C3033)

Every year the AAD05 will change to reflect the current fiscal year. So July 1st, the new account number will be 60190-AAD06-29900-0101-C3033.

I think if I make the accounts a lookup to the people table, then it will retain the historical data even when the accounts change. Am I correct?

I am trying to automate the changing of AAD05 to AAD06 for the 100 or so account records. I can't seem to find it now, but I recently read a topic about changing a portion of the field via scripts.

BTW--- I have changed some relationships based upon the P/F keys on Friday.

Please let me know if I could provide better information. This forum has been very helpful.


Link to comment
Share on other sites

Use fixed auto-entered serial numbers for the underlying relationships. These can then take care of themselves regardelss of fiscal year rollovers. The account number can then be changed at will without regard for breaking relationships.

Link to comment
Share on other sites

Thanks... that's what I did on Friday- P/F (Primary/Foreign keys).... but my questions remain ( I know I haven't been clear as I should be-- things are just so muddled in my mind lately..... ) :confused:

1) If I want to keep historical data... I should use lookups correct? desired result: when the acct changes, the previous year's contract assigned to a specific account won't change.

2) Do I use the Left function or can someone help me to automate the changing of part of the account number?

Thanks bunches

Link to comment
Share on other sites

(this chartfield is a calculated field).

If it has 05 and 06 in it, and that data doesn't exist anywhere, ie, it's not pulling the information from an existing field (whether creationDate or TransactionDate), it can't be a calculation and must be an Auto-Enter calculation on a standard text field. This is an important distinction. Can you clarify?

Good to hear you've incorporated unique keys as Vaughan suggested.


Link to comment
Share on other sites

Hi LaRetta,

The account table has the following fields in it:









the acctcode is 60190

class is C3033

deptid is 29900

fund is AAD05

progid is 0101

and the calculated field is c_chartfield and it’s calculation just concatenates the five parts (acctcode & "-" & fund & "-" & deptid & "-" & progid & "-" & class)

The automation should replace the Fund from “AAD05” to AAD06 or AAD07 (for 7/1/2006)

I really appreciate everyone’s assistance and advice.

Have a nice evening

Link to comment
Share on other sites

acctcode & "-" &

Left ( fund ; 3 ) & Right ( "00" & Year ( dateField ) ; 2 ) & "-" &

departid & "-" &

progid & "-" &


UPDATE: But again, you haven't specified WHERE the date comes from. A Lookup can't calculate. You need to use an Auto-Enter with this calculation to generate your account chartfield string when creating a job. What determines the fiscal year a job should belong to? I know your fiscal, but does that need to be pulled into the calc as well? And what happens if your fiscal changes ... don't laugh ... I've been there. Do you store this fiscal start date somewhere?

I also changed the calc ... it is still in the making, waiting for clarification ...

Edited by Guest
Added Update
Link to comment
Share on other sites

Well, if you know you won't change your fiscals, your Jobs Chartfield String could be:

accountcode & "-" &

Left ( fund ; Length ( fund ) - 2 ) &

Right ( "00" & Year ( theDate ) + (Month ( theDate ) > 6) ; 2 )

& "-" & departid & "-" & progid & "-" & class

Where theDate is either Get(CurrentDate) or based upon CreationDate or TransactionDate. This Auto-Enter calculation will set at the fiscal specified and then won't change. Don't uncheck 'Do Not Replace Existing Values.' You want to keep your accounts generic; in fact, the 0X could be dropped off. As I've said, the year should NOT be included in your Chart of Accounts. The DATA determines the fiscal year it should appear in. But your COA should span all years and remain non-specific.

Sometimes we can't change a business process easily so this will fix you up. I changed the calc so no matter how many characters in fund, it will only change the last two (I've made too many assumptions as it is). :wink2:

Link to comment
Share on other sites

This topic is 5716 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.