Jump to content

text calculation

This topic is 2598 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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? 

Link to comment
Share on other sites

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

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



_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



Link to comment
Share on other sites

This topic is 2598 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.