Script to count # of found records

[Marcel1]

Hi,

I am creating a database of gene sequences and am having difficulty with a script to name new entries.

My goal is to have a script to automatically assign a name to new entries, based on the values of other fields in that record. Each name must be unique. However, at times several records have identical field entries, and this would create identical names, so I am trying to automatically append a number at the end of the name, such as "01", "02", etc... The script should find all of the records with the same name base (ie all records that would otherwise have the same name), count them, and then add that value + 1 to the end of the name of the new record.

I have the whole naming part of the script finished, up to assigning the correct name base. However, I can't get the script to find records that have the same name base, and retrieve this number to complete the new name. Attached are my script steps. Any thoughts?

namesequence.pdf

[SlimJim]

What actually goes wrong with the script? I did notice a Get(TotalRecordCount) towards the end of the script. If you are trying to count a found set then you need Get(FoundCount) (or whatever is the equivalent in FMP6)

[Marcel1]

Yes, I just discovered the get(foundCount) and put this in instead, but the behavior didn't change. What happens is that the script performs the first name assignment correctly. then, the next time I use the script on a record that has the same name base, it just assigns another "1" to the name, instead of an "2". If I repeat with a third record, I still get the namebase with "1" appended.

Also, I am using the global field "buffer" to track whether or not records were found with identical namebases. I would expect buffer = "error " if no records were found, and buffer = "no error" if records were found. However buffer always returns the value "0".

Marcel

[comment]

Perhaps there is an easier way - see attached.

BTW, your profile says FMP6, while your script is definitely version 7.

appendUnique.fp7.zip

[Marcel1]

Hi,

this function seems to be doing exactly what I would like. Only thing is, i am not sure how it works... In this case, the FM help file has not been much help. In particular, what are

dup::SerialID

append::SerialID

Could you explain?

BTW, I have upgraded to FM7 but I can't seem to edit my profile yet. Perhaps they are working on this.

Marcel

[comment]

These are related fields. There are two relationships in the file. Open Define Database, go to the Relationships tab and see how these are defined.

[Marcel1]

Got it. In the relationships definition window, I gather that the three "tables" appendUnique, dup and append with the interconnecting relationships are not really tables, since there is only one table in the table list. What kind of data structures are they, and how do you make them?

Marcel

[comment]

That is correct. They are three OCCURANCES of the same table (a.k.a. self-joins).

See also FMP Help: Working with related tables and files > About relationships > About the relationships graph.

[Marcel1]

OK, I think I understand this well enough to implement in my database. One little question, though:

In the cNewName calculation, I do not fully understand what is going on with the case function. For case functions, I would expect to see a sort of "if then" structure such as "(test1; result1; test2; result2). But here, the test seems to be "Count (dup::SerialID) - 1", which just returns a number, so the conditional statement becomes something like

If "3", then result =

" " & Count ( append::SerialID ).

But wouldn't the "Count (append::SerialID)" return this value no matter what? In other words, why is the case necessary? I am sure it is, but I am just not seeing it...

Many thanks,

Marcel

[-Queue-]

If and Case tests are boolean in nature. If the test returns zero, then it is false; if it returns any other number, it is true. So, " " & Count(append::SerialID) is only returned when Count(dup::SerialID) - 1 is not zero, meaning that Count(dup::SerialID) is greater than or equal to 2.

[Marcel1]

Ah ha. Many thanks!!

[comment]

There's one thing that you should be aware of: if you ever delete or rename a record, the appended numbers will adjust to match. If that is not acceptable, you will need to go back to a scripted method - and a quite complex one too, I am afraid.

[Marcel1]

OK, thanks for the warning. I have run into a snag in implementing the method for my own database. I have created the same self-joining relationships and field definitions, and nearly everything works correctly. However, the cNewName invariably returns Name & "?".

I have traced this down to a problem with the Count (Sequence 2::SerialID) function (my equivalent of Count (dup::SerialID) which stubbornly refuses to give me a value. I am having a similar problem with Count (Sequence 3::SerialID), which is my equivalent of Count (append::SerialID).

I created dummy fields to display the values of these functions, and no value is returned (not even "0"!). This is despite the fact that there is a self-join between the appropriate fields in the table "Sequence" and the 2 other occurrances of the table, "Sequence 2" and "Sequence 3", as in your example, and despite the fact that SerialID has a value in each record in this table.

One thing I notice is that the relationship line diagrams look very slightly different in my database than in yours; the line linking Name in sequence 1 and name in sequence 2 (the self-join with the single "=" criterion) have flat ends consisting of short vertical lines, whereas in yours, they have the three-line triangular bird-foot appearance. This may be irrelevant.

Another difference is that my Name field is the result of a calculation from other fields in the record not involved in any of the above. I think this is also irrelevant. I tried deleting all of the records and creating new ones. I notice that the serialID does not reset to "1", but picks up where I left off, but there ARE values in each field. Hmm...

Also, isn't there a way to lock the value present in a field? this may be one way for me to use your method while preserving field name permanence despite later changing other records. I didn't find anything on this in the help section, but I may post this as a separate question.

Marcel

[comment]

I am only guessing here, but match fields for a relationship must be indexable, at least on the Child side (that would be the counted side in this case). If your Name field is an unstored calculation, it cannot be indexed, and the relationship will not work.

isn't there a way to lock the value present in a field?

Yes, but here's the snag: Suppose you run a script that sets another field, a text field, with the result of the calculation. This would make the result permanent. So far so good. But let's take a look at a situation where you have 4 records with the same name. Now you delete "Name 3". Now there are 3 records, "Name 1", "Name 2" and "Name 4". When you add a new record, it will be the fourth record in the series, so it too will be assigned the name "Name 4".

Or take a much more trivial situation: you have a single "Name" record, now you add another one. The new record will be "Name 2", but the original record is frozen at "Name".

This is actually not a Filemaker problem. You would have the same issues with any automated mechanism. I think you need to define your specifications more precisely. For example, if "Name 4" was deleted in the previous example - is it acceptable to re-assign the name to a new record in the future?

This requires very careful analysis of all possible scenarios, and finding a method that will not backfire under any foreseeable circumstances.

[Marcel1]

I am only guessing here, but match fields for a relationship must be indexable, at least on the Child side (that would be the counted side in this case). If your Name field is an unstored calculation, it cannot be indexed, and the relationship will not work.

Yes you are right: I changed the name field to a value that is calculated during an initialization script, and the relationships all work as they do in your example.

With respect to the other issue, ideally once a unique name has been used, it would be off limits, and adding another record would result in adding the next unused serial number. I might be able to compromise on this and accept recycled numbers, but I definitely have to avoid duplicate names for existing records.

These events will hopefully be rare, so perhaps there is a way to scan for duplicate record names, select the most recently modified duplicate, and reassign the name before closing the file.

[comment]

Well, that was only an example. There are many more possible scenarios to examine. This seems like a clasic example of how bugs come to be.

Frankly, the best solution would be to append the record's serial ID. This is, IMHO, the ONLY fool-proof method to ensure unique values. I do have a couple of more ideas - but I can also trip each and every one of them by some combination of deleting/renaming records.