• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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:
serial#&Tank&Rack&Level&Position

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.

0
nravida
Asked:
nravida
  • 2
  • 2
1 Solution
 
rogierCommented:
Hi,

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
Rogier
0
 
billmercerCommented:
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.



 
0
 
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.
0
 
rogierCommented:
Thanks nravida, and feel free to ask more help in case you can't make it work
rogier
0
 
billmercerCommented:
>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.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now