Creating and Counting Visits

[parima]

I'm two weeks new to FMP, so I am by all means very much a newbie. Originaly I thought this was a pretty simple problem but I'm no longer sure if it is. I've simplified my situation here; any help would be appreciated.

Basically, I want create a record with a visit number attached to it, serialized on CId's. I have a ClientTable with a unique CId and client information and a VisitTable with a visit number (N) and visit ID (VId).

A record in VisitTable holds: VId, CId, N, and other info.

VId = CId&N

CId currently is entered by the user and must be a value already present in ClientTable.

N should be 1 if the entered CId is not already in VisitTable, 2 if it already occurs once, 3 if it already occurs twice, and so on.

Since VId is generated off of N, and N is dependent on CId, N should not have a value unless CId has already been entered.

I've tried using the count(CId) function and making N a summary field defined as a running count of (CId). Neither seem to be what I'm looking for, and making N a summary field fills in a number before CId is entered and thus messes up VId.

This is my biggest dilemma. My second problem might actually make the first easier to deal with.

Is there a simple way to script a button to create a new record in a related table with a field already filled in? Specifically, from my layout displaying ClientTable data I want a button that opens a new record in VisitTable with the CId from the last Client I was viewing pre-filled into the CId field.

I really hope I made sense, any help would be appreciated, really. And, as I said, I've played with making some of my fields summary fields, but I'm definitely not understanding summary and subsummary fields in FMP yet.

-parima

[stanley]

Parima:

Create a relationship matching your CID to itself; call it Same_Customer. Your next Visit ID would be:

Case(Max ( Same_Customer::VID)<1;1;Max ( Same_Customer::VID )+1)

This will only work once the CID has been entered. You should set this value via a script with the above calculation in a Set Field [] step.

-Stanley

[-Queue-]

Or sort Same_Customer on VID descending and use

Case( not IsEmpty(CID); Same_Customer::VID + 1 )

and deselect 'do not evaluate if all referenced fields are empty'.