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

[agtjazz]

Hello--

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).

Question:

- 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

[Ender]

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?

[agtjazz]

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.

[agtjazz]

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

[LaRetta]

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:

[agtjazz]

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.

attach.zip

[Vaughan]

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.

[agtjazz]

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..... )

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

[LaRetta]

(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.

LaRetta

[agtjazz]

Hi LaRetta,

The account table has the following fields in it:

Acctcode

Class

Deptid

Deptname

Fund

Progid

z_acct_pk

c_chartfield

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

[LaRetta]

acctcode & "-" &

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

departid & "-" &

progid & "-" &

class

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 April 11, 2006 by Guest
Added Update

[LaRetta]

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:

[agtjazz]

Thanks… that is wonderful and works perfectly. I really appreciate all the help you guys in this forum have given.