Calculation Engine (Define Fields)

If you use GetAsNumber() with a date field is the number returned guaranteed to be unique on any given day? I'd like to use this value in a field with a CandidateID and an AssessorID to give a unique value for an assessment with a certain candidate and certain assessor on the assessment date. I know I will be asked why I am not using an auto entered serial number called AssessmentID, but I'd like to know the answer to this.

I have a database for calculating benefits for each of our employees. The benefit is based on what job classification someone has and how many years they have worked. I created a Case statement which computes how much vacation time they get in hours each pay day. All lines work fine and I have tried this on numerous examples with the exception of the last line. Perhaps I didn't end the calculation correctly??? It didn't say I was missing a ")" or ";" so I thought I was ok. But what happens if I pick "union" and the person has 10 or more years of service; it returns nothing. But if they are 1-9 years it returns the right hours. What am I missing??? …

I'm trying to create a report to compare inventory levels from one month to the next. I have a table where all the inventoried items are received and created (including the initial quantity) and another table where all the transactions (deductions, additions) are stored. The balance remaining for each item is calculated by taking the initial quantity from one table and adding/subtracting the amounts from the different transactions in the other table. Is there any way that I can 'hide' certain transactions (based on date of transaction) to be able to go back and recreate an inventory snapshot on a particular day. For instance, 'hiding' the July transactions to give a s…

This one has been puzzling me, but there is probably a simple solution out there that I have missed.. We have a table with all our staff, a table for shifts, and a table for dates in turn related to a table of jobs. Each staff member can have many shifts on many different dates. We want to pull a report that shows all staff that have worked their first ever shift within a date range we enter. I've thought about putting a first shift field within the shift table, which when their first shift is created it enters a one into this field - enabling us to search this field. However this doesn't work if there first shift is canceled or deleted after a second …

Hi Everyone. I hope someone will be able to shed some light on this one for me. I currently have a calculation set up to provide a numerical value. The equation is below. If(GetAsNumber(%) =""; Total Amount applied per day; Extend (Total Amount applied per day) * Extend (Retention factor)*%*1000/6000) I would now like to include an addition to this such that it will take into account a percentage 1-100% that will be included in an additional field so that it looks something like this. If(GetAsNumber(%) =""; Total Amount applied per day; Extend (Total Amount applied per day) * Extend (Retention factor)*%* NEWPERCENTAGE *1000/6000), where NEW pe…

I have a calculation field that has something weird... If it receives value '-1' (negative one) and gets added 1, the result is 1 (positive one), where it should be zero ( -1 + 1 = 0 ). Lower than minus 1 works fine, just the -1 screws up... I have to add an IF statement to screen for a -1, for it to set it to zero if 1 is added: If ( Guest | TA Towel Availability = -1 ; 0 ; Guest | TA Towel Availability + Traffic::Traffic | Towel Count ) Breaking my brains here, too weird for words! Filemaker 11 Advanced Mac

Hi, Not really sure if I posted in the right forum directory, but anyways... The problem I've stumbled across is "How to tell Filemaker Pro not to insert carriage return if the current line is the last one?". That is to create labels. My calculation field goes like that: Case ( IsEmpty ( First ); " " & Last & "¶"; IsEmpty ( Last);" " & First & "¶"; " "&First & " " & Last & "¶" ) & "" & If ( IsEmpty ( Nickname ) ; "";" " & GetField ( "Nickname" ) & "¶" ) & "" & If ( IsEmpty (Title) ;"" ;" "& TextSize (GetField ("Title") ; 8 ) & "¶")& "" & If ( IsEmpty (Company ) ; "" ;…

For some reason, various tables between records are adding connections between them on the database. The database is used to sort information from medical records. In the database, I have a portal table for medications, connected with auto-serial numbers. For some reason, random records (ex. #4 and #385) will automatically take the same serial number instead of its own unique value. This causes the tables to be connected and inserting information into one table changes information in the other record's table. Does anyone know why this is happening? Is there an issue with my database as a whole? How can I fix this? Thank you very much! -Mary

We have a special needs class that has special services provided. These services are povided on a "Service Date". I need to set another field based on the year of the Service Date (which is displayed as a normal date 7/1/2012), I have tried If [service Date= Year(Get(CurrentDate)] Set Field [unit Count; Service Units] but nothing happens. I thought this would check the Service Date field against the current Date/Year and if the year was part of the Service Date then the field would be set. But it does not work. Can someone assist? Thank you, Michelle

I’m trying to come up with a YTD balance on Personal time (which is a type of hours worked). I started by trying to determine the sum of HoursWorked when the Hrs_Type=”Personal”. This is where I am stuck. In this example my Starting Balance of Personal Hours is 32. Only two of my portal row entries have a Hrs_Type of “Personal”; one portal row with the entry, 8; and the second portal row entry of 3 HoursWorked—the sum of which should be 11. If I could get this function to work, I could then subtract that number (11)from the Current Yr Starting Balance of 32 resulting my desired YTD balance of personal time (which in this example would be 21). Can I entic…

Hi everyone! This has come up before so I wanted to throw it out for opinion if you would be so kind: I have standard one-to-many through several tables (which are tiers) as per attached. Normally Answers would only need the QuestionLabelID and would not need the QuestionID, SubCategoryID, CategoryID or AreaID because that information is available through the relationship. But sometimes one of the 'lower level' tables (which usually holds the most records, such as LineItems) will need an ID from up the chain. My question is ... is it better to A) place that AreaID also into Sub-Category as lookup from Categories and then Questions as lookup from SubCatego…

Hi all My company has 20 welders that use 5 different welding processes. We are required to keep a log on which welding process is being use by each welder per month. What I have a continunity log that shows the first day that the welder was tested and the last day that the welder welded using that welding process. I would like to print a report that would show all the dates that each welder used each welding process. I would like the report to be something like shown below Example: name GTAW GMAW FCAW Mark 2/11/2012 2/11/2012 3/14/2012 3/14/2012 4/12/2012 4/12/2012 4/15/2012 John 1/12/2012 …

OOPS, have just realized I posted this in the wrong area. I was reading a calculation post and forgot where I was. Is there a limit on how many Eles If statments a script can have (I dont think a Case Statement will work for our project). If I have counted correctly we will have about 20 Else If statments. If 20 is not a problem, then just for a reference, is there a limit? We run a Filemaker Server 11 and Filemaker 11 Pro on our workstations (not sure if there was a limit based on version). Michelle

I've spent 10 hours (including reading posts in the forums) trying to work out why my sums don't work. Forumistas have been generous in the past; perhaps you can also spare the time now to solve what must be a very simple error. I have a database of Fellows of an Oxford college, 1437-2012. There are 2,400 records. Fields include: Date of birth This is a calculated field: if we don't know the dob we use YearIn minus the average age at YearIn of fellows whose dob we do know : until about 1850 this is 22. YearIn a simple 4 digit year from records: when the person was admitted to the college YearOut a simple 4 digit year fr…

For quite a while, I've used an unstored calculation field to retrieve/display fields from a specific record within a found set. For example: Display::Name [unstored] = GetNthRecord ( Table::Name ; Record# ) Now I have a situation where I know the actual RecordID, but I'm drawing a blank on how to use it in a calculation. Is there a way to implement the above calculation using a RecordID? I'd like to avoid adding yet another Table Occurrence for such a small feature. Thanks in advance for any help.

There's a calculation function for WeekOfYear, but not WeekOfMonth. I was trying to find a forum topic on it last week and didn't see one, then I came up with a solution that seems to work well. First, create a field that calculates the first of the month for the given date in your original date field. So if the original date filed is called 'Date' and the new field is called 'FirstOfMonth': FirstOfMonth = GetAsDate ( ( Month ( Date ) & "/" & "1/" & Year ( Date ) ) ) Then subtract the WeekOfYear for the FirstOfMonth field from the WeekOfYear for the Date field: WeekOfYear ( Date ) - WeekOfYear ( FirstOfMonth ) + 1 The plus one is becau…

Hello I have some questions about images in FMP 12 that I'm hoping someone can clarify. I have a solution that was converted from 11 to 12. In 11 it had stored images in a conatiner field. I know that even after you set up the remote folder to store container field contents, for a converted solution, you need to actually tell it to move them. Q1: There is the option for storing thumbnails, does this increase speed for a solution hosted on FMP server? I've had a lot of problems with generating large PDF file sizes on some layouts. After extensive testing using GetThumbnail and not using GetThumbnail, I'm still confused. In general, it seems if GetThumbn…

I have a single field that contains a list of ten numbers that are separated by carriage returns. I need to put each number into a separate field. I would like to do this via a calculation. What would be the best way to do this? Example: field_a-d 8 345 12 19 Desired result: field_a: 8 field_b: 345 field_c:12 field_d:19

This was the best category I could find to post this... I've been storing documents related to my "Jobs" with each record having 3-10 documents. My file is roughly 1GB in size and growing fast. It's easy to drag and drop files into the fields from explorer, but I'm wondering if I should be storing them as a reference instead. Are there any drawbacks to storing all the documents in the database, other than the big file size?

I have a field: INPUT, and fields for Property Name, Address, City, State, Zip and Phone. I'm trying to parse this standard format for each record into the fields above: Complex Name: Property ABC Complex Address: 123 Main Street Complex City, State Zip: Miami, FL 12345 Complex Phone: 123-456-7891 If it matters, some of the phone numbers have extensions added as well. Also, when I paste this section into a field, the paste shows up like this: Complex Name: Property ABC Complex Address: 123 Main Street etc. It basically has a carriage return, and then 1 tab over to the info. Any advice would be greatly apprecaited... …

Hi all, I have the tables products and keywords. In this Each product have the multiple keywords. But I need to check the duplication for the keywords in the product. The same keyword can come in the another product, but should not in the same product. Please give me the suggestion to fix this. Thank you.

Is there a way to validate a calendar pick... wherein the enduser gets a message if the date picked is not a Saturday?

Hi! On a layout in browse mode I have added merge fields (based on an auto enter timestamp fields) to show creation and modification date/time for each record. That was very easy to do. But then I wanted to add something similar to my list layout, and I can't figure out how to do it. I would like to create a calculation field to show the oldest creation date (the oldest record) and one for the newest creation date (the most newly created record) for the found set in the list. I hope someone can help me with this:-)

Hey Everyone, I'm looking for a little help building a calculation to create a recurrent event based on the contents of a specific field. Basically what i've done is integrate a simple calendar solution into a database that tracks deliveries to my various customers. I've gotten to where my related delivery information is displayed in the calendar layout but i am looking for a way to set recurrent deliveries based on a Delivery::DeliverySchedule field. The values of this field can either be "weekly", "2 Weeks" or "4 weeks". So for example if i have a delivery set for today, friday june 1st, and the delivery schedule field is set to "weekly" i want another deliver…

I've got a work order database that shows a portal at the bottom. The portal shows shipper number, how it was shipped and the invoice number that shows the bill. I've tried to make a button on the number itself that is in the portal. Once the button is selected I would like it to goto that invoice number. I've tried goto related field, open, etc... I'm guessing because it's in a portal I can not make it work with out a calculation. Does anyone have a suggestion on how to make it work. Please see the attached picture for more info. See the attached.

Laretta was kind enought to show me how to pull data from one field "pastestring" into different fields. My question is, I have to different fields and will only use one of the fields per record. My calculation for the name field is: Trim ( GetValue ( Substitute ( GetValue ( pasteString ; 1 ) ; ":" ; ¶ ) ; 2 ) ) I want to say something like: Pull the name from line 1: on pasteString OR Pull the name from line 1: on pasteStringTwo. I basically have 2 different contact forms, and I just need to figure out how to put an OR statement in there... Any help would be appreciated!

This function is not working in a Filemaker 12 database: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) Typically it would create a list of field names on a specific layout. But it yields nothing. In another file it works just fine?

Hi there Forum I have set up a database to administer our product sales commission programme. Staff at clinics across the country sell items to clients and complete a line by line record of each individual sale they make which accumulates them 10% commission of the individual product wholesale price. I am using the 2 databases below to enable a field to have a running balance of the commission earned in Programme Lines as below: Clinic Personnel (containing <PERSONNEL ID> and <COMMISSION BALANCE> Programme Lines (containing <COMMISSION> and <PERSONNEL ID> The calculation I have used for the field displaying the total sum of co…

Greets, all: I have a quick question; please refer to the first of two attached screen shots. When the portal auto-creates a new record it automatically populates the Balance column's field with the Start Quantity value (from the upper-right corner); is there a way to prevent that from happening? It could be the way I have the calculation defined; please refer to the other attachment. Would coding the calc differently solve the problem? TIA for your help! Rich

A rank beginner here but a fast learner with a simple query. I have a field that constructs a unique URL for each record. I want a button that uses a script to open that URL. The open URL function is flummoxing me. What is the script? Thanks, Ted

let me start by saying i am new to filemaker and i am having an issue with a calculation field. I am trying to get a total count of units produced and the total footage for the previous days production run. I am trying to use the if statements below but its not totaling out correctly. Thanks in advance for the help... total record count for previous day - If(date=( Get(CurrentDate)-1);(Count(barcode));"0") total footage for previous day - If(date=( Get(CurrentDate)-1);(Sum(length));"0.00")

I have a field called accInterestEndDate with the following calculation: Let ( [ ~nextEntry = GetNthRecord ( date ; Get ( RecordNumber ) + 1 ) ] ; Case ( IsEmpty ( ~nextEntry ) ; portfolioDate ; Min ( ~nextEntry ; portfolioDate ) ) ) In the first record, date equals 01.01.2011. In the second record, date equals 02.02.2011. portfolioDate is a global field that equals 06.11.12. With the above calculation, accInterestEndDate in the first record should be 02.02.2011. However, it calculates to 01.01.2011 ( its own date field). I can fix this by changing the variable ~nextEntry to GetAsDate ( GetNthRecord ( date ; Get ( RecordN…

I'm trying to take text from one field and break it into several other fields. The source field contains several words separated by semi-colons, kind of like the format of a CSV file. Each semi-colon represents the start of a new field. The problem is that the values between the semi-colons are not a standard number of characters or words so calculations like LeftValues, MiddleWord, etc don't seem like they will work. I need a calculation that looks at all the characters from the first one in the field to the first semi-colon of the source field. Then the second calculation would take the values between the first and second semi-colons from the source field, and so o…

Hi, I'm pretty new to Filemaker and was wondering if you might be able to help me out with this. I need to convert a timestamp in this format- Sun Jun 03 2012 03:13:28 GMT-0700 (PST) into something that Filemaker would recognize (correct me if I'm wrong, but it needs to be in MM/DD/YYYY HR:MM:SS PM to import, and then you can display it in any other format afterwards, right?). I've looked around the forums, but having issues with the GMT offset & the month being letters instead of a number. Any help would be much appreciated, thanks!

Hi all, new to the forum and to filemaker, i have quite a bit of knowledge of ms access but new to filemaker, building my first database so just after some advise please i have a field (Text) which will contain barcode information the problem is the data i need in encased in the text, i need to remove the first 4 charters and the last 2 in access i used Left([Product],Len([Product],2) but not shire how i can do is in filemaker being so new any direct on how to active it would be fantastic many thanks in advance James

How do you copy and paste a field information such that i can then paste it to all of the other fields in that column? Like the copy and paste feature in excel.

hi if i have a filed of male name and a female name. in a calculation filed i want to count if there is two name i will get the number 2 & if 1 of the files is empty i will get the number 1 ? how can i do this thank you

I'm pulling some information from an ERP system which has Invoice Dates and Total Accounts Receivable. I would like to create a field that is able to identify the last invoice date of the prior month as I would like to set up a Table Occurance and relate this new field to the INV.DATE field of the table occurance in order to pull the Total Accounts Receivable from that date. Unfortunately as you will see in the example, the invoice date of the prior month is often not on the last day of the month due to weekends and holidays etc. I'm hoping this can be done with a calculation. If not and this requires a script, I'd appreciate any help with that also. T…

There is a text field Status and Drop Down List with different Statuses - High, Normal, Low. The idea is that when we change status in drop down list then to change and color of entire field. So, when Status is High then to be Blue, when Normal then Green and for Low to be in Red. No, cant use conditional script because end user might want to change and relate High to Yellow etc. Right now I use TextColor(UNDERLINES;RGB( Red ; Green ; Blue )) Which retrieve underline in different colors but that that is not good enough as it leaves white margins. Indeed, is possible anyway to set field in different color via script? Best Regards

I'm trying to send my purchase order using the realted field ::Email_suppliers. It opens Outlook and has the attachment etc but does not have the supplier's address in the address bar.

Hello , i like to know if there is a simple way to lock fields after creation, i need to lock the fields after getting the invoice printed. thank you ,

I have two times that I need to calculate the difference between. This is never more than 24 hours. StartTime and EndTime are four-digit numbers (integers) representing 24-hour military time. The FileMaker time field, while it could support 24 hour time, was too buggy with copy/paste and the colons between hours, minutes, and seconds. I've changed the user-entered fields to text fields validated as "numeric only" between 0000 and 2359. Working to add the ability to prevent times like 2284, but the time function will actually account for that. Anyways, my calculation to find the different between StartTime and EndTime is working, as long as the EndTime is greater than…

Brain Freeze!! Help! I am trying to do a calculation to get the number of visits from my reports table summarised by date. I will explain the scenario; When salesmen visit customers they log reports against them. These could be multiple reports on a single day. I have been told that its safe to assume there is only one customer visit per day for any given customer. The sales person may do multiple reports for a visit he/she is doing on a particular day. I need to get the number of total visits per customer, treating all reports (tagged as 'visits') for a particular day as one visit. The relation is Customers to Reports on customer code and repor…

I have a Purchase Order system that uses a serialized value to track PO records. After record creation, this number is attached to a julian date to make a po number like this: 06052012-xxxxx, where xxx is the serialized value. Everything is fine at this point. The serialized value has never had a problem making the secondary PO number. However, when going back in to some of these records, I'm finding that the serial field sometimes reverts to 1. I really dislike when issues are sporadic. Any body ever have this issue? I could write a script that would parse the PO number and reassign the correct serial, but that will not help this from happening in the futu…

I have the customers ID number stored in a variable, how do I enter this into the calculation field so the new layout displays the customers details. I've included a pdf of my script. I'm not even sure if this is the right approach to do this. Customers.pdf

I have a deadline approaching and I'm freaking out, help! How do a define a field or fields for pre-paid accounts. Which will be used like a bank account. I have used the invoice starter solution as a starter for this database. I have the following tables: customers payments invoicing products thanks in advance

I'm trying to add greater than a year periods to a starting date. I use a calc field, result date: Date ( Month ( startdate ) ; Day ( startdate ) ; Year ( startdate ) + Period ). Works fine if years are 1, 2, 3... and so on. But what if the period is 1.5 years or 3.5. What should I do? Thanks.

Hey guys, I've developed a small application used by data technicians (me) on film sets which keeps track of how much data has been transferred and stored from the camera magazines. I have a portal where the tech can enter how much data was transferred, whether it's in kb, mb, gb, tb, etc. I need to develop some way for the user to define if the data transferred was in megabytes, or terabytes, (etc), and for a field to calculate the total transferred for the day. I want the field to be able to automatically trail the total with a "mb", "gb", or "tb" depending on how big the total is (kind of how Finder on Mac can determine how much space is remaining on the hard driv…

Ok, rookie question here. I'm new to FileMaker, I've been using it for about 2 weeks. I'm working on a solution that was created for the company I'm working for by an outside consultant. What I need to do is organize the parts list for products. I want the products to be sorted into a list so that the end level parts are listed, with all the components of each part indented below them. Sub components of components are also indented below the respective component they are part of. Each product has an End Part field that defines if the given part is an end level product, as well as a parent field, that shows what part it is a component of. There is also a…

I'm very new to FM How do i get a count field to restart after it reaches 11? I want a field that auto-enters a number on report creations. I want it to start at 1, but after it reaches 11 i want it to restart at 1 again. Second question: I have 2 tables, #1 customers (parent table), #2 orders (child table) how do i create a calculation field thats sums up the total of a customers last 10 orders. Only the last 10 orders according to the date field in the order table? Thank-you in advance, im very new to this, if been seaching for both answers but cant figure it out.

I have these two columns: "products_model" and "products_image". The "products_image" takes the model number and adds ".jpg". Works fine, but it generates this for ALL records. I only want it to write the string for products for which I really have an image. "products_image" is an calculation field: "covers/" & Products::MANUFACTURER & "/" & Products::PRODUCT & ".jpg"

Hello all i am a novice user of fmp 12. i have this question in my mind can't figure out i have 2 tables as follows: table A named "Alphabet" with 2 fields ----> "Letters" , "Value" table B named "Data" with 2 fields ----> "Words" , "Value" in table A i have all the letters of the alphabet in the "Letters" field , and in the field "Value" a number for each letter. I want in table B to enter words in the "Words" field , and when i do that , i want in the "Value" field of table B to automatically filled with the sum of the letters of the word i enter. for example: table A A=1 B=2 C=3 D=4 E=5 F=6 G=7 table B…

Hello , i would like to know if i can convert a field into a bar( black rectangle) when it gets some typed on it. example: field1 = " empty field" ---- no black rectangle field1 = " whatever data on it" -------- shows black rectangle thank you very much M

I have a very simple use of a repeating calc field that I need help with. I have searched this forum and found some pretty good examples of recursive functions etc., but just can't seem to get my head around them, and they don't really address my simple needs anyway. I am integrating to a third party dbase (via ESS) that is not normalized and has multiple fields for phone numbers in their "contacts" table. I want to be able to match against any of the phone number fields, from a single phone number field in my solution. My thinking was to make a repeating calc field that would combine the 4 phone number fields. I thought that simply doing something like . . . num…

I know it's simple, but text parsing isn't my thing... Any help would be appreciated. I have three fields, one is City State, and then City and State, I just need it to copy the city into the city, and the state into the state. Thanks!

Hey all, Does anyone know if there is a way to set up FM so that the modification date can be overriden? IE: I have a field that is set to auto-enter the modification date. Sometimes a change is made and then undone so I would like to be able to manually change this date. The field should still auto-update when the record is next modified. any suggestions? Forgot to mention I'm using FM11.

I have a calculation field called feesSummary which aggregates the data from a series of other fields depending on whether they are empty or not. However when all referenced fields are empty the hard coded currency text surrounding the fields (eg "$" & fieldName & ".00") still displays though the if statement should result in "". Here is the calculation code for the feesSummary field: If(IsEmpty(flatFee) = 0; "$" & flatFee & ".00" & ¶; "") & If(IsEmpty(fullFee) = 0; "$" & fullFee & ".00 " & feesNotes & ¶; "") & If((IsEmpty(fullFee) = 0 & IsEmpty(vipFee) = 0); "$" & fullFee & ".00/$" & vi…

Is there a way to enter percentages without the decimal in front? IE; when a formula contains a percentage, say 10%, is there a way I can just punch in 10 rather than .10 and get the correct answer? Thanks in advance, Charlie

Hi, I have a problem with auto code for product in subcategory. Please see my attach database, My problem is:for first of record in each category , can't take a code and code start of second record. how I can do this? I want product take auto code according category code and unit of record in category. TestHosting.zip

Hello everybody I need some help with a calculation field. I have a currency field that holds a calculated value. The decimal separator is European: a comma. Because it is a calculated value the number of decimals are not always 2: It could be 1000,5 or 1000,52 or even 1000,5232198 My problem: i need to export the field to a MySQL table the format should then be: 1,000.00 (US currency, with two decimals) Can anyone help me with a calculation that: - sets the number of decimals to 2 - converts the comma to a point Great! Dick

FileMaker 12 includes an undocumented Get ( UTCmSecs ) feature, which can be used for timezone-independent timestamps with millisecond precision: GetAsTimestamp ( Get ( UTCmSecs ) / 1000 ) = Greenwich mean time to 3 decimal places. This is great for sub-second timing and for determining the timezone of client and host devices, as the GetUTCOffset custom function illustrates. This is a great feature to have; but, as many developers have noted, it isn't supported and there's an above-average chance that it wont exist in future versions. I don't like either of those things. This is functionality worth having, so lets ask for it! Here's FileMaker's feature re…

Hello: My question is on how to ensure that a plain number field (CountCasesResult) is displaying the result of a calculated field (CountCasesCalc ) whenever changes occur to that calculated field - automatically. I'm trying to speed up a list-based summary report that I created with the help of Matt-squared (Matt Navarre, Matt Petrowsky). I was manually running a replace command, but ended up creating a new calculated field, which is becoming a real drag. I'd like this to be automatic, like FileMaker's indexing of fields for calculation purposes. Currently, I have a self-join in the data file, along with these 3 fields: CountCasesCalc - count of "ca…

hi, i am using FileMkaer pro 11 advanced my application is of size 27mb ,the performane of application is slow ,how we improve the performance of application is there any software or any technique to enhance the performance of application

Hi, every one. I have a problem, for Product's code. I want give code to product according product's category. I mean, I have : category table(catID,CategoryName) in this table have some record (Board,CT,Cable,..) Product Table(ProID,catID,ProductName) in this table some record and link two table (category::catID - Product::catID) I want give to this product a code like this: if in category table (Board's ID=1) then in product table, productID=11 and for second product in this table product'ID = 12 and go... and if in category table (CT's ID …

Hi everyone... got a simple yet no so simple question... what would be the script to add a year minus one day to a date? Like in insurance policies which expire one year less a day from the day you bought it? Seems complex because some months have 31 days.. some 30, some 29 on leap years n 28 on other.... Thanks!

Hi I am trying to use the getsummary function to use a summary in a calculation. The database is managing products produced on board a freezing vessel. I have two tables: ProducedProducts with: id fk_trip_id no_of_produced_cases weight_of_case total_weight = no_of_procuced_cases * weight_of_case fk_process_method fk_type_of_productd and Yield with: id fk_trip_id yield_of_products fk_process_method fk_type_of_product Then I have a report with a subsummary showing records from ProducedProducts Subsummary by fk_type_of_product Then I have Total of no_of_produced_cases, Total of total_weight and Average of yield_of_…

I am working with an inventory database where each record has a number which is formatted with a letter prefix followed by a number, sometimes sub-numbers. example TS.1 TS.2.1 TS.2.3 TS.10 TS.15 TS.15.1 TS.20 My issue is now sorting it as it appears above, whether I set the field type as number or text, it still sorts like this. TS.1 TS.10 TS.15 TS.15.1 TS.20 TS.2.1 TS.2.3 Any suggestions on how I can sort them as if they were files in finder on a mac. Thank You.

I have a portal that shows me the packing slips of a job and it includes the fields "billed checkbox" and "quantity shipped" fields. I frequently partial bill these jobs, so some of the packing slip records have the "billed checkbox" checked from previous partial bills. I would like a "partial bill qty" field on my invoice to auto enter only the sum of "quantity shipped" if the "billed checkbox" is unchecked. So far, I've only managed to come up with calculations that keep summing up all the fields and I can't figure out a way to omit the values in the "quantity shipped" fields if the "billed checkbox" is checked. Any ideas?

I have a table that has four date fields & four time fields, corresponding with the stages of a job. Let's call them Stage 1, Stage 2, Stage 3 & Stage 4. I need to ensure that the stage Stage 4 date & time >= Stage 3 date & time, stage 3 date & time >= stage 2 date & time & so on. I can validate by calculation that one date is >= to another but I can't work out how to do it in conjunction with a time. Can anyone point me in the right direction?

I'm trying to create a filter field that works as follows. The relationship is between a User record and multiple Project records. Each Project record contains a text field listing all users and their respective project roles. When a user is added to or deleted from a Project record, a script is triggered to update this field. Every project user name is listed followed by the user name, a dash, and the project role assigned. So the field might look like this: John Smith John Smith - Sales Jane Doe Jane Doe - Technical Support On the User record side, I am creating a "dashboard" interface for each user that will display a bunch of different information…

Hello everyone, I am new to filemaker pro but improving everyday. I had a question about counting. I have created a database about question and answers. There are hundreds of questions in the database. These questions belong to 10 different subjects. The user can select the subject that he wants to take the test from I have a count field which counts the total number of questions and when the user changes the subject it results in change in the count appropriately. That seems to be working fine. I have given every question a unique number say 1 - 100. But when the candidate changes the question; instead of the unique number I want it to display as Questi…

I have a calculated field that adds 21 years to a child's birth date in order to ascertain the date on which they turn 21, then subtracts from that the date of a parent's death, resulting in the number of years that the law would have required the parent to support that child (that is, until the child's age of majority -- 21). However, if the parent's life expectancy is less than the result, then the parent's life expectancy replaces the result (the parent can't support the child any longer if the parent is deceased!). Here's my dilemma. If the difference is equal to or greater than 21, I want the result to be zero or the field to be left blank, since after age 21 …

Hi All, I have a customer table that has a relationship to a sales order table based upon customer ID. I am trying to create a calculation field that would flag the customer as being in the top five customers of all total sales. Not sure if this makes sense but I have included my sample file anyway. Anyone have any ideas? Thanks -T FMP 10 Advanced PC/Mac Top Five Sales.fp7.zip

Hi everyone, I am using filemaker pro 11 advanced ,my problem is that i am using global field (idd) to store last record and in new recored i am increasing the idd field by one ,the script which i attach is working fine but when the filemaker is forcely quit the idd global field is not giving the proper results.can anyone know why its happening

I'm struggling to understand an intermittent problem using the substitute function. I have a script that works on a single record extracting data into a global text field, The script adds the item description on the first line, a list of serial numbers on the second line, and a order reference on the third line, so the final text field might look something like this. Bds-C-Z-2-S-351/364-0.8-Ferrule s/n 176613.176666.176619.176616.176615.176617.187934.187937.187939.187941 w/o 8220623 When I initially add the item description to the text string, I apply the Proper function to it to to ensure that the item description has a consistent look: set fie…

Hey all. I have a time field where the duration of a film is entered in HH:MM (1:30) format. Sometimes we get films in that just have the minutes listed (90). I'm looking to incorporate a script where the user clicks on the duration time field (as a button) and is presented with a dialog box asking for either the duration minutes in one field, or the hour and minutes duration in another. If the HH:MM is entered then the time is directly entered in to the original field as the user entered it. If minutes have been entered, I want a script to convert the time to HH:MM. For example, if 1:30 is entered in the duration, it stays as 1:30. If 90 was entered in to the durati…

Hello , i would like to know if i can set a field to show 0 or nothing if the value is a negative number? please let me know thank you Mauricio

Hi, I have a table for items. Items are defined by a Class, Type and Color combination. Items have a price. For instance: Item1: 1, 3, 4, 4 (Class, Type, Color, Price). Item2: 2, 3, 6, 8 Item3: 2, 3, 6, 2 Item4: 1, 3, 4, 2 Item5: 2, 3, 6, 12 Item6: 2, 1, 1, 6 What I need to obtain is the average price for each Class, Type, Price combination, not in a report, but in a field within the table. The field should update every time a price change or a new combination is created. In the sample above it would be: 1, 3, 4 = (4 + 2) / 2 = 3 (average price for item with combination 1, 3, 4) 2, 3, 6 = (8 + 2 + 12) / 3 = 7,34 2, 1, 1 = 6 / 1 …

I have a table containing individual income or expenditure records for individuals, and a table with a layout to display the individual's details with a portal of their income and expenditure amounts. I cannot work out how to get the syntax for the calculation field to show a running balance for the individual that will show on the portal under his/her name. Please can someone help? I've attached an example of what I'm trying to achieve - the running balance field is currently empty! KB Finances.zip

Hello, I currently have the following calculation: If (Item Received = "1"; Get (CurrentDate)), i.e. when I click a checkbox, the field with this calculation displays the current date. Is it possible to modify this calculation so that the date I get when I click the checkbox is the last day of the current month? For example, I check the box on May 3, 2012 and the field displays "May 31, 2012." Thank you!

I would like to insure that a record could not ripened until a future date . People could. Change the date on their systems . I want to find a sure-fire way to get the accurate time on the Internet so there would be no cheating. Any suggestions are very appreciated .thank you

Ok, I'm drawing a blank on how to handle this... I added a table of zip codes, which also have fields for the longitude and latitude. That's rougly 80,000 records. I have the calculation to find the distance between two zip codes (based on longitude and latitude). Now, the hard part: I have a Jobs table, which has a zip code field, and a Vendors table which has a zip code field. When I'm in the Jobs layout, I want to have a portal that shows all my vendors in a 100 mile radius. Any ideas?

I am looking for a hundred-per-cent-effective method how to number continuously (avoiding gaps and repetitions) subsequent records in multi-users environment? I am sorry – I am not sure whether my question is clear or I use proper wording. So let me use example to show my problem. Suppose, we have a database of invoices in the form of a table. Each record in the table represents one invoice. Many users have access to the database at the same time and can create new records (register invoices). Each record must be given a unique ordinal number. The number should be given at the moment of saveing (entering) record, not at the beginning of it's creation – thus I do…

Hi Gurus, Trying my hand at the new Filemaker 12 executeSQL function, but can't seem to figure out how to do a count based on 2 conditions. I have a projects table and I have a related project contacts table. A project can have many project contacts related to it and they can belong to different "types" of contacts. The choices I have for the types are Bill To, Ordered By, Ship To and Install To. The user can add as many contacts to a project as needed, but if they have more than one "Bill To" type I want a script trigger to fire and ask them to choose which one they would prefer to use for the pulling in the tax rates and terms. The only reason I don't mak…

Hi Ive had a look through the forum and can't find the keyword that I am looking for so I was hoping someone could help. I have the trial version at the moment and I am trying to add a custom text box under my fields where I can add comments then put a date stamp etc.. by the side. I just need to know what it is called please so I can research and learn how to use that section to see if it will do what we want before purchasing. Thanks in advance for your help and sorry if this is the wrong section of the forum. thanks Dave

I have a table that shows expenses, it has 2 fields one field is the amount (number field) the other field is whose expense it it, (text field) and the whose expense is a drop down menu with 2 selections which are Scott or Matt. How do I set up a calculation that calculates all of the records with the drop down Matt selected, and vice versa all the records with Scott selected. Thanks

I'm making this difficult i'm sure... All I want to do is take this: Name: Jonathan Smith Address: PO BOX 1 City: Dallas State: Texas Zip: 12345 and paste it into a field. Then I want fields: Name, Address, City, State, Zip to just in the value. Any adivce? Thanks...

Four of the fields on one of my layouts are used in an auto-calculation function: Age, number ParentalStatus, text, indexed ParticipantStatus, text, indexed ParticipantStatusIdentification, text, indexed auto calculation Case (ParentalStatus = 1 or Age > 17 ; "Participant" ; "Child" ) The above case statement is used to automatically re-evaluate every time the Age field or ParentalStatus fields change. If the participant is a Parent or they are over the Age of 17 the ParticipantStatus field is automatically changed to “Participant”, otherwise “child”. This works great. However, I need to make a correction and include Staff as a third category …

Hello I wondered if it is possible to have my filemaker file somehow import a sound file into the database via a script from a web page? I have no idea if this is possible. I would like to match data in a field and then have filemaker download and appropriate sound file from the internet. Maybe this is for a plug-in but I thought I would start here and ask. Summary user enters data in a text field. for example. door slam, baby crying, applause Then hit a script button and if they wrote applause in the field "Sound" it would go to a preset URL and import the so named sound file from there and then it would permanently reside in the FM file. Thanks for …

I have a series of calculations that are stored as containers. They all say: If ( field > 10 ; Image On ; Image Off ). I need a function that can "Count" how many Image On's there are on each record. I'm not an expert with the Count function, if that's the best one to use, and the examples of it's use online seem to be sparse... Any ideas? Thanks.

Hi, I want to import two number strings and make one new string The strings are like this two 6 figure numbers 125678 875643 I want to take the second third and fourth digit of each number 125678 875643 and create two new numbers 256 756 The numbers are the result of a lookup and normally you would return the full 6 digit number but I just want the 3 digits from each... How do I do that ? Thanks.

I would like to restrict certain functionality depending on whether the host or a client is trying to access it. I thought I would compare the 'host name' to 'client name' or 'host IP' to 'client IP'. I know where I can get the host name and host IP address for the database, but don't see a way to 'Get' either of the client data. If they aren't available, how do I test in a script whether it is the host or a client that is accessing the script?

Hi All, I have adopted "Event Management" starter from FMP11 and I came across with this very strange problem which I spent so much time trying to figure it out but no success. I have created this relationship "Contacts::k_ID_Contact ad Events_Guests::kf_ID_Contact" with the objective of counting the number of events attended by a contact. I have created EventsAttendedCount in the Contacts table and assigned a calculation "Count(Events_EventsGuests::kf_ID_Contact)" but to my surprise it's not do the calculation. Please look at the attached file and tell me what's wrong with my calculation. Thanks. EM.zip

Anybody taht can help? I have a table with a code where the number of characters on right is fixed (15) but the letters on the left changes. It's the code for the stock options. I need to create a field where any number of letters on left will be shown alone. A120621P00040000 DVN120519C00085000 AMZN120519P00010000 A DVN AMZN Thanks, Samuel

I have two tables and I'm trying to figure out the caculation so that I can display the number of customers that a salesrep sold to in the (slsmcustcnt) table pulling from the (slsmdetail) table. (slsmcustcnt) and (slsmdetail) are joined (slsmnum) field (slsmcustcnt) has 1 field "slsmnum" with 3 records W1 W2 W3 slsmdetail has 4 fields "slsmnum" "custnum" "custcnt" and "sales" This table has several records Example "slsmnum" | "custnum" | "sales" W1 | AA10 | $1 W1 | AA10 | $2 W1 | AA20 | $1 W1 | AA30 | $1 W2 | AA40 | $1 W2 | AA50 | $1 W3 | AA60 | $2 So…

Hi, I am experiencing a strange problem related to the format/representation of dates, where on some computers the day of month is being interpreted as the month. Here's what I have: - A field named "ReportDate", of type "Text", with the properties "Auto-enter" and "Calculation replaces existing value". The specified calculation is: MonthName ( Self ) & " " & Year ( Self ) - A "Drop-down calendar" control, backed by the "ReportDate" field Basically, this implements a calendar control where only the month and year are relevant, and the day of month is disregarded. For example, suppose someone chooses the date "April 24, 2012" using the drop-down …

NOW IN SHOW DIALOG BOX SHOWS ENTER ID_PK there i want the id_fk should be entered automatically :grad: qrcode.zip

Is there a calculation that would let me create 12 fields that I could use as headers in a layout where the first field would display the prior completed month name and then the remaining 11 fields the month names before that? So as we are in april: Field 1 | Field 2 | Field 3 | Field 4 | Field 5 etc... Apr | Mar | Feb | Jan | Dec

I am trying to figure out a way to separate out a number/modifier from the beginning of a field entry. For example in these two entries: 25 MG Valium 2 MG Niacin 25 MG I want to separate the leading '25 MG' from the Valium entry into a new field called Quantity. I tried this: Position ( drugfield ; " MG " ; 1 ; 1 ) ≤ 10 and PatternCount ( drugfield ; " MG " ) = 1 ; LeftWords ( drugfield ; 2 ); only to realize it picked up Niacin in the second record. Some quantities can have long numbers, eg 2000000 UNT so it is very variable. Is there a way to test if the first position is a number? Other ideas? Thanks for any assistance.

Once my enduser picks a date from a calendar popup I would like to automatically populate two fields: a fiscal year and fiscal quarter. The fiscal year runs from July one year to the end of June the next. July - Sept = fiscal quarter 1 Oct - Dec = fiscal quarter 2 Jan - Mar = fiscal quarter 3 April - Jun = fiscal quarter 4