Number self join records

[cmcfarling]

I need to number records in a self join relationship. A simplified table might look like this:

JobNumber Department Number

12345 Sales 1

12345 Sales 2

12345 Shipping 1

12345 Sales 3

A self join is created on the JobNumber AND Department. The Number field is an auto-enter calculation. The first occurance of a particular relationship has to be Number 1, the second Number 2 and so on.

The problem I'm having is calculating Number 1 for the first occurance in a relationship. Obviously no records match the relationship prior to the first record being created. I need to test for that condition where no records exist in the relationship and then set the Number field accordingly, as an auto-enter calculation mind you.

If I use

If ( Count ( TableSELFJOIN::JobNumber ) = 0 ; 1 ; Max ( TableSELFJOIN::Number ) + 1 )

Filemaker appears to not evaluate the formula because the Number field ends up empty. To simplify and test this, I shortened the formula to simply

Count ( TableSELFJOIN::JobNumber )

however that returns an empty field as well.

So I tried

IsEmpty(Count ( TableSELFJOIN::JobNumber ))

to see what happens and that return zero. Likewise

IsValid(Count ( TableSELFJOIN::JobNumber ))

returns 1.



So, something is being returned from

Count ( TableSELFJOIN::JobNumber )

in this case but it's not a value that I can test against it appears. I've also tried

Count ( TableSELFJOIN::JobNumber ) = ""

and

Count ( TableSELFJOIN::JobNumber ) < 1

to no avail.

[Raybaudi]

Isn't enough:

Max ( TableSELFJOIN::Number ) + 1

??

[LaRetta]

This can break in multi-user mode. I suggest instead using a calculation and relationship to simply display the related numbers you need.

[cmcfarling]

Max ( TableSELFJOIN::Number ) + 1

Evaluates to an empty field as well

[cmcfarling]

This can break in multi-user mode. I suggest instead using a calculation and relationship to simply display the related numbers you need.

I'm not clear on what you mean. Why can it break in multi-user mode?

Please clearify "simply display the related numbers you need"

[LaRetta]

Wow. Where do I begin ...

Why it will break in multi-user mode: When user creates a record (and before they commit the record), FileMaker doesn't see it through a self-join. Another user can also create a record and that first record won't be counted and the second record will be set with the wrong number.

The problem I'm having is calculating Number 1 for the first occurance in a relationship.

Why would you need to? If you place those self-join records in a portal and place the @ sign next to them, they will number them for you. And the same is true if your relationship is filtered by type, such as Sales, Shipping etc ...

You can - at any time - know the record number of related records. If you write that record number as data into a record and the record is deleted, what do you plan to do? Will you reserialize all the records? I truly didn't read through all of your attempts of producing a count. But it is probably because the record wasn't committed yet. And I truly don't understand the purpose of your need ... there may very-well be a valid need but I suspect there are simpler ways of accomplishing what you wish.

Can you provide more information for us? There are many wonderful people here who would provide great ideas for you.

LaRetta

[cmcfarling]

Regarding the multi-user mode issue, the question is when does FMP evaluate an auto-enter calculation, on record creation or record commit. If it's on commit then I dont see that as a problem. Does anyone know the answer to that?

Here is why I need to do this. When a record is created the Number field is significant because that will be used as an identifier for that record. The first record created is Number 1, and always needs to be Number 1.

Say 3 records exist with Number field set to 1, 2 & 3 repectively. Now the second record is deleted leaving

record 1 - Number = 1

record 2 - Number = 3

I need to be able to reference the second record by it's Number field, 3 in this case. Using the @ method does not allow that.

[comment]

when does FMP evaluate an auto-enter calculation

An auto-entered calculation is evaluated on record creation, or - if all referenced fields are empty AND "Do not evaluate if all referenced fields are empty" is selected - as soon as ONE of the referenced fields is populated.

If you don't mind gaps in your numbering, then why not simply use an auto-entered serial number?

[cmcfarling]

In fact I do mind numbering gaps and ongoing serial numbers are not an option. Each group of related records has to begin with Number 1 and count up from there.

I actually solved the issue anyway. Deselecting "Do not replace existing value for field (if any)" and "Do not evaluate if all referenced fields are empty" allows the following formula to populate the Number field

Max ( TableSELFJOIN::Number ) + 1

The big hangup was not having "Do not evaluate if all referenced fields are empty" unchecked initially. After unchecking that, I thought all would be well. Oddly, even though the Number field is emtpy upon creation, "Do not replace existing value for field (if any)" has to be unckecked as well in order for this to work.

As for multi-user concerns, the records will be created through a portal. There is no chance of User-A creating a record and not committing it before User-B creates a record. FMP will display an message box if User-B tries to alter the same record that User-A is actively in.

[comment]

I don't think you have followed LaRetta's point through:

There are 6 records in a category. User A creates a new record, and that record calculates its own number as 7 (6 + 1). Before User A has committed the new record, User B creates ANOTHER new record in the same category. This record sees 6 committed records, so it too calculates its own number as 7. When both users commit their respective records, you will have a duplicate.

There are other issues as well:

Say there are 6 records in category A, and 3 records in a category B. User creates a new record, and he accidentally chooses Category B instead of A. The mistake is corrected immediately, but the field has already calculated 3 + 1 = 4, so now there will be two records #4 in Category A.

In fact I do mind numbering gaps

Well, then what you're going to do when a record is deleted? Earlier you said that deleting a record SHOULD leave a gap. If that gap is permissible, why not other gaps?

Consider also this: there are 6 records in a category, and record #3 is deleted. In this case, there will be a gap in the sequence and #3 will not be re-issued again. But if someone deletes record #6, then Max ( related::Number ) will drop to 5, and the next record will be #6 again.

In short, the requirements for (a) permanent numbering, that is ( unique and © depends on other records are mutually exclusive. Pick any two out of the three.

[Vaughan]

"As for multi-user concerns, the records will be created through a portal. There is no chance of User-A creating a record and not committing it before User-B creates a record. FMP will display an message box if User-B tries to alter the same record that User-A is actively in."

I'm not sure about this.

Easy to test though: open a second window and try it out. IN one window, add a new portal row but don't commit it (leave the cursor in a field). Then in the other window, create another portal row.

Let us know how you fare.

[comment]

Actually, if you are doing this in a portal, it won't happen because the PARENT record will be locked.

[cmcfarling]

I understand LaRetta's point. My point is that Filemaker won't let that happen though, so it's not an issue. In this case the user creates all records through single portal. The only way to create a record is by typing into the portal's empty row. Filemaker won't let 2 users create a new portal row at the same time.

I misspoke when I said I minded numbering gaps, wasn't think apparently. Also,I have considered the scenario where record 6 gets deleted, record 5 becomes the MAX and record 6 gets issued again. I don't think that's going to be a problem, I'll ponder it some more though.

[Vaughan]

Thanks for the correction Comment. :)

[comment]

The only way to create a record is by typing into the portal's empty row.

That's not something I would want to depend on for such a vital requirement. Data integrity should be ensured independently of workflow and user interface, that might change in the future.

[cmcfarling]

"I'm not sure about this.

Let us know how you fare.

I did in fact test this by sharing the database over the network and trying to edit the same portal row from 2 machines at once. FMP won't allow it.

I took this a step further and discovered something more. I created two separate portals that both access the same group of related records. One related record, and therefore one portal row, already existed. That was Number 1. On ComputerA I edited a new portal row in Portal A but did not commit it. On ComputerB I edited a new portal row in PortalB.

At this point in time both portals had uncommited changes in them. The expected behavior, once both were commited, would be that I would end up with duplicate Number fields. Two Number 2's in this case. That was not the case in my testing however. It appears that FMP did not assign the Number field until the commit event. So the premise that duplicate Number fields are a potential issue appears to be false, at least in this situation.

Note that the relationship has "Allow creation of records in this table via this relationship" enabled.

[cmcfarling]

That's not something I would want to depend on for such a vital requirement. Data integrity should be ensured independently of workflow and user interface, that might change in the future.

What's wrong with creating records via a portal? I've used this method many times with no unexpected side effects.

[comment]

I didn't say there was anything wrong with it. I said I wouldn't let my data integrity depend on the assumption that records will be ALWAYS created ONLY in a portal, from now on and forever.