Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Looking for a better way to transfer data


Jabert
 Share

Recommended Posts

I have a file containing seventeen data fields that can vary from time to time. For example, the number of children can change when they become adults.

 

I need to capture historical slices of the data based on the date. To do this I have created a mirror image of my primary file. I want to write a script that will enter the data in all the fields in the second table reflecting the state of the data in the first table on a week ending dates.

 

I know I can write a script that goes to the first table, copies a field then goes to the second table and paste the data in to the corresponding field.

 

Example:

Go to table 1

Go to field 1

Copy field 1

Go to table 2

Go to field 1

Paste

Go to table 1

Go next record

etc.

 

Doing this takes eight steps per field, resulting in a very lengthy script.

 

is there a shorter way to do this?

 

TIA

Link to comment
Share on other sites

I think you may want to consider a different structure. I don't quite understand what you DB does, but it would seem to me the easiest way to search and find this data would be to have it in a related table.

If those fields were in a related table, and every time a field changed you would create a new related record (think of something like a transaction), then a report on that related table, on a particular date, would give you the info you want.

When you create a new related record, you could auto-enter either all 17 fields, then change the ones you want, or start with a blank record, and auto-enter all the empty fields to the values in the previous related record.

But then again, I'm just assuming.

Please provide more info about your DB.

Link to comment
Share on other sites

The reason I cannot use a related table is that a change in the primary table would also change the historic table. What I have to have is a way to produce reports that cover a period of time (e.g. monthly and yearly reports). Let me be clearer.

 

 

I need a report that shows the number of adults, children, and those over 55 who were served by month. The primary table (the one that contains fields such as how many adults, children an Seniors there are in a family) will change form time to time. Thus, if I have a related record where the number of children (or any other group) changes, then the related record for a previous date will also change. If that happens, then the report for any previous time period would use only the current number of family members, not the number that was for the previous count.

 

 

Do I have you totally confused? I am reluctant to upload my current file as it is pretty rough looking and is evolving quickly.

Edited by Jabert
Link to comment
Share on other sites

1. Don't use Copy/Paste. Use Set Variable/Set Field, or, if the tables are related, Set Field (YourTable::someField; RelatedTable::similarField). Copy/Paste puts data on the user's clipboard, and may interfere with another program in the background which may be using the clipboard. Set Field keeps data inside FileMaker.

 

2. Don't duplicate data. If you think you have to, it is usually because your primary table's structure is wrong.

 

Without knowing anything about your data, you would want static data (name, dob, etc) in a primary table (call it People) , and changeable data in related tables. Each related table record will have a foreign key (which is the ID of the specific People record), a date, and contain only the data that has changed at that time.

Depending on your data, you may have any number of related tables (one for Children, one for office visits, etc) with each related table having fields which pertain to that general topic (e.g. Children:dob, Children:gender)

 

If you give more specific information about your data, we can give you more specific advice on how to model it. What are your fields, and which of them are likely to contain data that changes (that you wish to keep historical information about)?

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use