Replacing records

Posted on 2006-04-18
Last Modified: 2010-04-27
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.

Question by:nravida
    LVL 4

    Accepted Solution


    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
    LVL 19

    Expert Comment

    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.


    Author Comment

    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.
    LVL 4

    Expert Comment

    Thanks nravida, and feel free to ask more help in case you can't make it work
    LVL 19

    Expert Comment

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Title # Comments Views Activity
    Primary 1 59
    FilemakerPro 10 archiveing 2 37
    Filemaker Pro - Rounding time 2 52
    Filemaker Server 14 - Webdirect NAT access 8 73
    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now