>Auto-Enter or Lookup?
Example file:Constant.fp3
This month I’m going to revisit the problem of marking the first entry of a unique value in a field. There are several reasons you might want to do this. A common one is to show only unique entries in a portal, by relating a “1” constant field to the marking field. Otherwise you often have duplicates showing, making the portal useless as a value list for making choices. I’ve recently discovered another method, using an Auto-enter by calculation, one of the Auto-enter Options available in Define Fields.
Lookup Method (earlier article)
In the earlier article, I showed how to use an Auto-enter Lookup to put a 1 in the marker field for the 1st entry, then lookup a null value (nothing) for any subsequent duplicate entries.
This works well enough, but it has the serious limitation that you absolutely cannot reset or Relookup the key* field later. If you do, for some other use, forgetting about the 1st Mark lookup, the mark will be erased. All the records having that value will no longer be visible in the portal.
*A key field is a field used to define a relationship.
Auto-enter by Calculation Method
This new method solves that problem. Auto-enter by calculation is a little tricky to implement, but once you understand the timing, it’s pretty easy. The problem is that auto-enter by calculation is normally triggered immediately upon creating a new record.
Marking the first entry relies on a self-relationship based on the key field you are marking for uniqueness. The field is empty when you create a new record, so there is no self-relationship to evaluate.
Delaying the Auto-enter
The trick is to stop the auto-enter from calculating until the field(s) is filled in. It is similar to a more basic trick used to make an auto-enter of a concatenated* field wait until both fields are filled in. All that is needed is to stop it from occurring if one of the fields involved is empty.
*Two or more fields put together.
In this case we also want to stop it from happening if the self-relationship is valid; because this would mean there was already a match, an earlier duplicate value.
In the example file, the field we want to mark is Job ID.
The marking field is FirstMarkCalc =
Case(not IsValid(Self\Job ID::Constant) and not IsEmpty(Job ID), 1,
Case(SerialID = Self\Job ID::SerialID, 1, “”))*
*The second line is not totally necessary for the mark to occur the first time, but is useful if the record is deleted (more below).
Deleting 1st Mark!
There is one problem with any of these methods. If you delete the first record of the group of records containing one unique value, you’ll delete the mark (of course); and it won’t re-evaluate. Neither method will.
You might think, “Just use a calculation that will re-evaluate.” Unfortunately, because it would depend on a self-relationship, such a result could not be indexed. It couldn’t be used as the target of any relationship, making it useless for many purposes, such as portals.
The only thing you can do if the mark is deleted is to run an operation on those records to reset the mark into the first of them. It’s easy to isolate them, using; Go To Related Record [Show, “Self-Job ID”].
Replace 1st Mark, Lookup
With the Lookup method you can run a Replace, by calculation, on the FirstMarkLook field, using;
If (SerialID = Self\Job ID::SerialID, 1, “”)
The mark field must be on the current layout and enterable!
Replace 1st Mark, Auto-enter Calculation
I added this step directly into the definition for the auto-enter calculation method. So all you have to do is Relookup on the Job ID, or Replace it with itself (these are pretty much the same operation), and the mark will be reset. In this case, the Job ID must be on the current layout.
It comes to the same thing, but I like it built directly into the definition. Another advantage is that if have more than one mark field (more later), they will all be re-evaluated and reset.
Button for Deleting Records
It is important that you make a button on the layout for deleting records, so that it can check for the mark before deleting, then perform one of the above operations afterwards if needed.
Since the Job ID of that record will no longer be available to use in the self-relationship after it’s deleted, we need to set it into a global field first. Then we’ll check for that after deletion.
[This time we are using the Job ID g field only as a “flag” field. It isn’t doing anything else but letting us leave a flag before we delete the record. We could also create a new global flag field to do it. It is very important to “initialize” (set to nothing) such a flag at the beginning of the script.]
The subroutine would look like this:
Set Field [“Job IDg”, “”””]
If [“JobMarkCalc”]
Set Field [“Job IDg”, “Job ID”]
Exit Record/Request
End If
Delete Record/Request [No dialog]
If [“not IsEmpty(Job IDg)”]
Go to Related Record [Show, “Self\Job ID g”]
Set Field [“Job ID”, “Job ID”]*
End If
Exit Record/Request
*This step will only work with the Auto-enter calculation method, not with the Lookup method (which will not lookup a new mark). With that method you’d have to explicitly set the marking field, using this step instead:
Set Field [“Marking Field”,”1″]
Set vs. Replace
Notice, in the script above, that I just used a simple Set Field on one record instead of Replace on the whole group. This works because going to the self-relationship automatically puts you on the first record of that group.* As long as you are just deleting one record at a time and have only one marking field, you can just reset that one. It’s much faster than a Replace on the group.
*If, however, you’ve sorted that relationship, as you can now do in v.4 or 5, it will go the first record in that sort order, which probably won’t be the first record that was entered. In the case when you just want to mark one of each group, but don’t care particularly which one, this doesn’t matter.
If it does matter, you can duplicate the relationship, but make the new one unsorted; use that one for the Go To Related Record [“Self-relationship”] steps. Or, if this is the only place where the problem occurs, just add these steps before setting the Job ID field:
Unsort
Go to Record/Request/Page [First]
One other advantage of using Set Field, rather than Replace or Relookup, is that it doesn’t require the key field to be on the current layout. The auto-enter by calculation doesn’t require the mark field to be on the layout, either. This makes it even safer.
Imports or Mass Deletions
If you import or delete many records at a time from different groups, or if you have multiple marking fields from different combinations of self-relationships (see below), then there is usually no alternative but to run one or more Replace operations. If multiple groups are involved, you can still do each separately, if you know which ones. If not, you’ll have to do it on all records, which can take awhile.
Concatenating Fields for Multiple Marks
Since Replacing into the one key field will reset the marks, it is possible to have more than one mark field, and still be able to reset them all if one of the marked records gets deleted.
The other mark fields would be based on concatenations with the key field. In the example file, there is the field, Job\Person ID = If(not IsEmpty(Job ID) and not IsEmpty(PersonID), Job ID & ” ” & PersonID, “”)
The “not IsEmpty(Job ID)” parts ensure that the field is only filled when both its part fields are filled. This is important, as otherwise the mark calculation could not tell which was the first entry of both parts.
Its marking field, Job\Pers\MarkCalc =
Case(not IsValid(Self\JobPers ID::Constant) and not IsEmpty(Job\PersonID), 1,
Case(SerialID = Self\JobPers ID::SerialID, 1, “”))
This is the same as the earlier calcs, but in this case it uses a concatenated field.
Replace Deleted Mark with Concatenated Fields
This is basically the same as Replacing into the Job ID field for the single mark, since we built the “Serial ID comparison” step into the definition again. It is different, however, because with 2 fields being used as the key, if a record is deleted, it’s more difficult to tell which records to go to before running the Replace.
The general rule is go to the related records for the “encompassing” key field. In the case above you’d go the records for the Job ID, and do all the records for that job. Basically, it works easily if there is a “one-to-many” relationship between the two groups. In the case above, many people work for a company, but each person only works for one company.
If each person can work for several different companies, you’ve got a “many-to-many” relationship(s), and things are considerably more difficult to mark; though not by any means impossible. You’d have to capture the IDs of all companies the person worked for, and do the Replace on each company group separately; or just do all records. You’d probably want separate files as well.
New Records Methods
By the way, the example file is also good for an earlier article on creating new records. It was such a simple file that I didn’t bother to post it earlier. If you didn’t read the article, you can download it from the FileMaker 101 Collections page. It was Part 17. Earlier articles on creating 1st Marks were, I think, Parts 9 and 10. There were also articles on Portals, etc.. You may as well just get the whole shebang 🙂
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.