text calculation

[madman411]

Hi all

 

I'm attempting to write a simple database to help me keep track of reports at work. I work in the film biz and I'm going to be implementing a way to track my camera logs on my ipad instead of filling out paper sheets. I first thought that using the serial number feature would suffice, but I believe this to be a little more advanced than what that feature is capable of.

 

For my example I will call this report "Report A". I have a field named "media label" in a table called "report entries", and upon the creation of a 'report entry' I would like the following:

 

Report A:

 

The media label is created using the following info: 

 

An abbreviation of the show name (lets use "Big Brother" as the example) - so "BB" is the abbreviation. I have a field where the user has defined the abbreviation for that particular production. "Abbreviation"

The date without formatting (todays date as the example) - 062014. Upon a new log being created in a separate table, the system date is entered into a "date" field. I need the calc to strip down any formatting for this particular calculation.

There are separate reports for each camera. In this case we'll use camera A. A field is defined for the user to enter the camera letter/number upon the creation of a new report (per camera) for the day. "Cam letter"

And finally, the roll number. Let's assume 01. Every new day starts at roll 01.

 

We would format this media label as: BB_06204_A_01.

 

I need FMP to detect if any previous records for the day have already used the next roll number rather than just advancing (say if I then created roll A_02 and then deleted it, FMP would know roll two needs creating again instead of advancing directly to roll 03.)

 

I will be implementing an overwrite field should the user purposefully want to over write the calculated media label.

 

Can anyone suggest a text calculation to combine this information appropriately? 

[Wim Decorte]

abbreviation & "_" & 
Right ( "00" & Day ( theDate ) ; 2 ) &
Right ( "00" & Month ( theDate ) ; 2 ) &
Right ( Year ( theDate ) ; 2 ) & "_" &
cam_letter  & "_" & 
roll_number

The complexity is not in this calculation though but in how you generate the roll_number.

 

I use the below calculation in the attached file.  If find the most recent used roll number for the given camera and date.

However this will fail if:

- you create rolls #1, 2 and 3 for camera A today

- you then delete roll #2

- the next number will still come up as 4, not 2 (if that's what you are expecting)

- if you then manually re-use 2, the next number will come up as 3 but that is already used

 

So you have think through the logic here and adjust the code to fit your need

Let(

[

_roll = _FieldRef_getField ( FMforum92283::roll_number ; True ) ;
_table = _FieldRef_getTO (  FMforum92283::roll_number ; True ) ; 
_date = _FieldRef_getField ( FMforum92283::theDate ; True ) ;
_cam = _FieldRef_getField ( FMforum92283::cam_letter ; True ) ;

_sql =
"SELECT " & _roll & 
" FROM " & _table & 
" WHERE " & _date & " = ? AND " & _cam & " = ?"  ;

_result = ExecuteSQL( _sql ; "" ; "" ; FMforum92283::theDate ; FMforum92283::cam_letter ) ;

_max = GetValue( _result ; ValueCount( _result ))


];


_max + 1

)

FMforum92283.fmp12.zip