Replacing records

I am working on a scientific database that will store data and inventories of samples.

As of now I have a freeze sheet for every sample/vial I have frozen
I have a button on the freeze sheet that makes a inventory record from a concatenated field of:

Occasionally we thaw a sample.  In the inventory there are the seperate Tank, rack, etc... fields, the concatenated field and other data.   We want to keep the position data (tank, rack, etc...) filled in but the rest of the data we have being replaced with the word empty as it is thawed.

I need to ammend my script that creates a new record or goes to the related record from the freeze sheet to the frozen inventory.  I would like it to recognize a concatenated field I have made for the freeze sheet and frozen inventory consisting just of the Tank, rack, level and position = F.I. unique field
Basically I want it to say:
 If there is already a record in the inventory matching this F.I. unique field then
replace that record with the data from the record I am currently working on.
 How would I  script that?
I don't want to lose the data from the original freeze sheet that is why I chose to have two unique fields but I am not sure whether this will work.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I do not fully understand your business but perhaps the following can help you with your Filemaker application.

The script would first copy the new data from the record into global fields or perhaps your let the user enter the data directly into global fields (the latter won't work if you want to store these data for use later on). Typically you would use the "set field" script step

Secondly, select the set of records you want to check for presence of certain values in the unique field and check the field. To do this, you could make a loop and inside that loop a "if...then" function. The loop would end with "end loop (exist after last)" script step.

If the "if...then" function finds the data you're looking for, use a couple "set field" steps to change the data in that record for the global data. Don't forget to finish your script with a commit record script step.

Let us know if you need more specific instructions or an example script.

Good luck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You have a variety of "locations" which consist of particular positions in various tanks. This is probably relatively consistent over time. You also have a set of samples, which probably change more frequently, and have a relationship to the locations that is probably fairly arbitrary. In other words, it sounds to me like you have two different kinds of data here. Usually that suggests that you should have two separate tables.

My suggestion would be to split this data into two separate tables, one containing the slots/locations where samples can be stored, and the other containing the actual information about the sample, with a location ID used to connect the two.

By doing this, you allow the database relationship itself to take over some of the decision making. Calculated fields could automatically adjust the displayed text on the fly as you make changes, without having to use a lot of scripting. This would also allow you to keep historical records of where samples were, which might be helpful if someone makes a mistake, or if you want to see which freezers have the most activity, etc.

nravidaAuthor Commented:
Unfortunately, we use this table as a way to quickly glance at available resources for samples and as an inventory.
If I just have the location and serial # it doesn't help me to know what type of samples I have of a certain category.
I know this is not an ideal way to set up the database but it is more important to have the quickest way to scan the info we need. So I would like to attempt to keep the info I have and see if I can make it work.
Thanks for the help.
Thanks nravida, and feel free to ask more help in case you can't make it work
>I know this is not an ideal way to set up the database but it is more important to have the quickest
>way to scan the info we need.
There's no reason why it would be slower or more difficult to view the information with a more normalized structure. If anything, it would be faster, since it would require less string manipulation.
But it would involve more work to set up initially, so if this more of a throwaway database that you don't care about in the long term, it probably isn't worth it.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.