Calculation Engine (Define Fields)

I have a fmp6 db that keeps tracks of requests made.the requests have codes and they are all unique. I also capture the dates these requests were made. Now I would like to create a report that shows the number of requests made each year and even go back 4 or 5 yrs.The format looks like: 2006 2007 req1 34 25 req2 37 3 and so on. for instance, I have a calculated field Case(request_code = "CLT" and Year(Date requested)="2007",1) and then a summary field that gives the total count of the calculated field. It seems to work fine but with one problem. Any time a request is made/selected from a valuelist, the summary field has t…

I would like to display a series of asterisks in a calculation field to flag certain conditions. For example, If (Weight<500,Weight,"*****"). Since this is a number field, it doesn't display *****. Is there a way to do this?

I have a field which has 23 numbers and I would like to remove the first 10 from the field. for example the number is "27457839462738493823432" I would like to have the following remain in my field "2745783946273" I understand how to find it with right function but not how to remove the 10 digits from the right. Thank you

I want to replace the last two characters in a field called "goal_score_perAction_textversion" with the value of the field called "score". eg: goal_score_perAction_textversion "g100_s45" score value is "70" I want the result "g100_s70" My current calcualation; Replace ( action_scores::goal_score_perAction_textversion; Right ( action_scores::goal_score_perAction_textversion; 2); 2; action_scores::score) is producing this; "g100_s4570"

Hi There, I am trying to have text auto entered into "Field A" if "Field B" = "Active" So, basically if "Field B" = "Active" then "Field A" would have a warning automatically filled out that would say "This record is currently ACTIVE" I am able to accomplish this with a script, however I would like to have this done automatically if the conditions match. I am wondering if this can be accomplished with a calculation field. Thank You.

Hello eveyone, I am trying to trim out the field that contains full urls into only domains. Eg. http://subdomain.domain.com/contact.html http://images.yahoo.com/userx/folder/image.jpg http://www.google.com/search?q=filemaker&rls=com.microsoft:en-us&ie=UTF-8&oe=UTF-8&startIndex=&startPage=1 into: http://subdomain.domain.com http://images.yahoo.com http://www.google.com so I found this works: Trim ( Substitute ( MiddleValues ( Substitute ( DestinationURL; ".com"; "¶"); DestinationURL; 1); "¶"; "" ) )&".com" ------------------------- ------------ but how to implement some form of variable to cover all domain exten…

Hello Team, I'm working on a database that takes attendance data for a school to generate reports. At this point I can identify different types of attendance data, and report on how many instances happen on Mondays, Tuesday, Wednesdays, etc. I can also identify the week with the most attendance entries. I would like to be able to refine this report to specific instances of data. For example, a "tardy" is "Absence Code = A", an Unexcused Absence is "Absence Code = B" where Absence code is the field name and the "A,B,C ... J" identifies the type of attendance entry it is. I identify days and weeks with the following calculations; DayOfWeek(Absence Dat…

Hi, Is there a function that would check if a value is numeric? I need this to check if certain characters in a text field are numbers. The text field is usually text, but sometime it is numbers followed by the letter "e" (100e, 200e). My calculation goes as follow: Let ( [ endE = if (Right (streetName; 1) = "e"; True; False); len = Length (streetName) - 1; /*Check this line for isNumeric*/ isNumber = if (isNumeric (left (streetName; len); completeStreet = if (endE and isNumber; streetName & " " & roadType; roadType & " " & streetName) ]; completeStreet ) Why this? In Fre…

I am creating a FM database to track various statistics for golf. Here is what I am trying to do. I have it set up so that each round is a new record. Within each record are scores, number of putts, club used etc, specifically defined for each hole (score 1, score2). What I want to do is to evaulate each record for how many times a specific club was used, the distance it was used from and weather it was accurate (these are all differnet fields: club1, green1, yardage1...) I want the report to list the clubs along with thre related stats. Any ideas? Thanks, Greg

Hi everyone, I would like to ask a simple question about making price to text. For example, If the number is 100, I would like to make it as "one hundred", 1000 as "one thousand" and so on. Can anyone help me on this? I'm sure there's a built script in this forum, but I can't find it. Any help would be appreciated. Thanks

Hi all. Is there a calculation which is will extract from a URL such as: http://www.mydomain.com/folder1/folder2/page.html the domain only? The result should be like this: http://www.mydomain.com/ I have been searching and trying out some calculations, but unsuccessful. Any help would be great. Greetings Greg

Hi, This all happens in the same Table and TO. Field1 contains a number provided by the user (1 to 15). - All records in the TO have a number in this field. - Several records might share the same number (there might be several records with 1 in field1, and several records with 2 in field1, etc.) Field2 must be automatically calculated based on field1. The calculation should be: Field1 & "-" & Record Position according to the number of records of same type present when creating the record (a typical serial number). For instance, 1-1, 1-2, 1-3, 1-4, 2-1, 2-2, 2-3. When the user creates a record, he/she must select a number in Fie…

I'm working through 'Using Filemaker 8.5' (Love, Lane, Bowers - don't they sound romantic?). Trying to implement a report where you can sort by column and the sorted-on column changes colour. This uses a script which is passed a different parameter depending on which column heading is clicked. Behind each heading is a global container field with a calculated value, such as: If($$columnsort="OrderCode";gHighlight) - where gHighlight is a global container with a coloured box pasted in, and $$columnsort is set by the script parameter. These calculation fields are set to container, global storage, and 'Do not evaluate if...' is unchecked. For some reason, …

I would like to put a recording button and a playback button in a layout. I would like scripts to "record" a sound into a container and a script to play the sound back. Can a record be scripted somehow? Thanks

I am trying to calculate a paid-to date but have struck a problem when a person pays by instalments eg: Month - April Start Date - 1/4/2007 (the first day) End Date - 30/4/2007 Payment Due - $1000 at a day rate of $33.333333 (ie $1000/30days) 1st Instalment paid is $750 which gives the number of days at 22.5 (ie $750/33.333333) So the paid to date becomes 22/4/2007 2nd Instalment paid is $250 which gives the number of days 7.5 and a paid-to date of 29/4/2007 NOT 30/4/2007. The problem is the half day which is rounded down by FileMaker. If I round up the paid-to date becomes 1/5/2007 which is still not 30/4/2007. Any ideas on how to handle this rou…

I have what would be classified as a "text serial number" that I need to increment by 1 upon certain user actions. The field needs to display with leading zeroes. For example: "0001002" would increment to "0001003". Can this be done without using a custom "pad text" function?

for want of a better way to describe it. Can someone find a good solution to this problem? The values contained in a field in a related table might look something like this, if listed: 1 2 3 4 7 9 10 11 12 I want this: 1-4, 7, 9-12 I have actually succeeded, using a looping script and a calculation field, but the calculation is very cumbersome. Also I wonder if it couldn't be done without a script - just with a calculation. Any ideas? Thanks in advance!

Hello, I am very new to Filemaker. I used it for a long time, but this past week I have been developing a database for our business. Anyways, I have a timestamp, it is in our "notes" field. The purpose of this is that people call back, and we need to document the call, etc.. different people accept different calls. I learned today that the time stamp workds great the first time, but then when someone in the same day tries to enter a 2nd one, it erases the first one??? Anyone have any answers on that? Thanks so much in advance.

Hi, I am building a duty calculator and have attempted it but it's stack full of multiple fields and multiple case statements. I am unfamiliar with let statements/custom functions but wondering if they would be of value to use, and if so, how. When the following protocol is assesed it is on the "fee" EXAMPLE 1 fee = 15,000 (rate 1 applies) 15,000/100 = 150; 150*1.5=$225 EXAMPLE 2 fee = 156,000 (rate 4 applies) 2350 + ((56,000/100)* 3.25) = $4170 [color:purple]RATES [color:purple]Not more than $20,000 $1.50 for every $100.00 or part $100.00 More than $20,000 but not more than $50,000 $300.00 + $2.25 for every $100.00 or p…

In my invoice table when new invoice is created a PO number is automatically assigned (PO number creates records in my line items table, if no PO number is created in invoice table, no records can be created in line items table). I have an auto enter serial number which acts as Invoice number as well. Every time new invoice is created the serial number is increased by 1 - it is just a regular number. PO number takes that serial number and adds the first 3 letters of the customer company as a prefix. So when creating a new record the generation goes like this (assume it is the first record created and two customers are in play called x and y) 1st record…

I have a bunch of buttons that are on a layout - but there are certain conditions ( one of the buttons) that I would need the user not to be able to activate the other buttons when one button is pressed. In other words if script 4 is activated by this buttons I would like to know if script 3, 5, 6, and 7 could somehow be rendered inactive temporarily. thanks Dave

Hello I would like to arrange my filemaker windows for different machines. Is there any way for filemaker to "memorize " the window locations so I can just leave it as is. Not having to write in location data for each one.

Does anyone know of a way to make a timeline. I want to show events of a persons life in a timeline . I would like to do it in filemaker. Maybe there is a plug-in for this. I just wondered if anyone has made one in filemaker. Thanks

Hi collective wisdom! After a few months, I am looking at FileMaker again. I'm trying to create a db where I can enter a date in one field, and then display in another field the day of the week ("Tuesday" etc). Then I could sort to find a subgroup of all events that happen on a Tuesday. I've looked in the FM 8.5 help files, but I seem to be missing a step somewhere, because I just get "?" in the calculated field. I've got my DayofWeek1 field set up as a calculated field. I've got a field named Date1 into which I enter my desired date. Then the field DayofWeek1, type Calculation, =DayName ( Date1 ). What am I missing? It seems like it ought to be easy, but I've looked at i…

Okay, next question: Is it possible to use the value in a timestamp field to derive a date or time value for another field? I.e., timestamp field value is "2/15/06 3:15pm", calculated fields might be Date2 with value "2/15/06" and TimeMed "3:15pm". I did look in online Help, but didn't see anything along those lines. Any suggestions appreciated!

I am needing to import two fields from an Excel sheet which contains 25,000 records, of which only about 10,000 are unique. The import will create these records in my database for the first time and I just don't want all the duplicated records to be created as well. How do I only import the unique records OR how do I delete the duplicate records in a script if I have already imported them. Seems easy but I must be getting foggy with my logic skills. Thanks

I have a field that I want to use to record the account name of the user who modifies a refund field. The calculation looks like this: [color:red]Case ( Refund = "Yes"; RefundEnteredBy = Get ( AccountName ) ) By default the refund field is set to 'No'. Therefore, if the user changes that to 'Yes' then I want the RefundEnteredBy field to record who changed it. So far, all this does is return a zero and not the account name. I've tried altering this many different ways with the exact same result. All zeroes. Any thoughts? Thanks.

I'm Back I have a DB for Condo projects I manage. Have a table calle Units Have a table called Owners When a unit is sold, A new owner record is created. When I go back to the unit Layout I need to see the current owner. What i get is the first Owner of the Unit. I don't think the LULast trick will work. Would like to do it by a lookup but can't seem to make that work. Any suggestions? Thanks again all.

Hello I'm a complete noob, just figured out a little about containers and calculations but don't really understand how to do this... or what the reference path should be. I have 2000 people in a database All their details showing up nicely For most I have a photo for each (in a folder on my computer called "staffpics" Each photo is titled as "FirstName (space) LastName.jpg", where FirstName and LastName are each fields in the profiles * I just want their photo to appear when I skip through the profiles. (extras... would be great if when clicked it opened up the original photo - probably would be bigger. would be great that if a photo didn't exis…

I have a contact file in Filemaker 8 where the default area code is set to 916. Oftentimes it's easier to create a new record by duplicating an existing record because much of the contact information is the same. However, if the area code was changed in the original record, it is overwritten with the default of 916 when the record is duplicated. How can I avoid this?

Dear friends please help me find a way do base64 convertion, I know there are plugins that have this functionality but I don't need all the extra features of them. The string:

Hi Guys, Im stuck with this calculation (even if it is one). I have a 'street location field' which is a drop down - from a merged field containing the street & suburb into the one field. This is created via a portal. I have a design run sheet that displays the locations etc that was put in from the portal. But In the run sheet, I dont want to display the 'merged field' instead just showing me the street (without the suburb). How do I go around this??

I've not had any luck trying to figure this one out - my calculation skills are minimal! I have a Calculation (thanks Ted S /Genx) for a field called 'KN Prefix' which works just fine: Let ( [ dotCount = PatternCount ( VFX::KN Input Temp; "-" ); dotPos = Position ( VFX::KN Input Temp; "-"; 1 ; dotCount ) ]; Left ( VFX::KN Input Temp; dotPos - 1 ) ) However if a field called 'Slate' in the same table is empty, I want this calculation to either stop or if a value has already been entered in 'KN Prefix' I need it to be cleared. In simple (non FM) terms its needs to do this before the calc: if Slate is empty then clear KN Prefix and sto…

I have a classified advertising database and I need it to figure the following for an ad based on word count. It costs $40 for a minimum of 25 words and an additional $1.50 a word over 25. I would like to be able to keep it where the dollar amount and word count can go up over time. Thanks...and I am extremely new to FMP.

I am trying to prevent editing of records in browse mode. I set the file to be opened in read-only access mode. That prevents editing of records. I also made two buttons named Edit and Commit. Basically they have re-login scripts attached to them that will re-login the user without a dialog between read-only and data-entry access modes. The reason is as I will have quite a few people browsing the records daily so I want to prevent accidental changes of data. When in read-only mode I would however like to get rid of annoying "your account level does not allow this action" message and I would like to lock the fields so that they cannot be selected much like field behav…

Is there a way to set a field that receives a calculated result so that it can be edited (deleted and a usere defined result entered if needed)? Thanks

How can I take pieces of fields to generate an ID? Example: First: John Last: Smith Company: Every Company Street: 123 E. Main St City: Springfield State: IA Zip: 54321 ID: EC123IA (Company-Street-State)

I've got a table called Parts. This table includes fields for Cost, QTY, and a field call IsCredit that gets 1 or 0 if the record is a credit to our account or not. The idea behind the check box is that if it's checked then the number in Cost needs to be negetive to reflect that in our actual cost of the item. So as it stands now I have the Cost field set to the following auto-enter by calc. If ( IsCredit = 1 ; Cost * (-1); Cost ) The problem I'm having is that the calc. doesn't fire but every OTHER check of that check box. For example... I have a record that I go ahead and check the box on. Then I fill in $5.00 for Cost and it corre…

Hi All, I have written a basic script in One Tab - that would perform the find, paste it into a field. The problem im having is, after I have performed the find and pasted what I've wanted, I want to go to the next tab which is where I can view the run I want. How do I go by doing this? Thanks

Hi I am having trouble getting serial numbers to work correctly. As per Help and two manuals, I follow the procedure of having a serial number created upon Commit, and when I create a new record the new serial number is created, but if I back out of that record without Committing, the serial number stays incremented. The only non-vanilla thing I am doing is inserting text into fields in that record first - might that be committing it in some way? Greg

Quick question! (sort of) I have a layout that needs to contain word documents and pdf's from clients... creating some containers and putting in each file would work, but there are too many files for some clients (some clients have close to 100 documents)! I guess I need a way to store multiple docs in one container or create containers dynamically... Or perhaps there is something else I could do. Any suggestions?? Heres what I want: 1) EASY access to many word docs and pdfs from filemaker... 2) to log all changes and created documents (via scripting?? etc)... Thanks! -Chris

I've been stuck on this calculation for a while. I need to enter into one field the oz of an item, (the number of ounces). In a second field, I would like it to automatically calculate how many milliliters that item is. I have the actual numeric calc, but I cannot get it to work in the table. For the "ml calculation" field. . . I have put in: (oz Entry * 29.57352957) + ml Entry. I have also changed the Calulation to numeric. Any suggestions? Thanks. . . ;)

Hi, Can an unstored calculationfield with an aggregate function in it (count) be used as a trigger for an auto-enter (calc.) field. The fields are in the same table. So far I did not get it to work. Henk

Hey guys, I'm making a private database for a meeting which has members and then attendance tables. In the members tables i have multiple names become the members can be considered more as families. In the attendance table i select the member ID which then brings up all the names of the family. I want to have checkboxes next to all these names so i can identify which people from the family actually came. any way to do this which could possibly be used in a find script? if not doesnt matter im mostly looking just for a checkbox so i can identify which individuals came from the member group... Cheers, Simon.

I'm trying to give a field a certain status if a field in a related table is not empty. I've tried this calculation but nothing happens. If ( not IsEmpty(Order::Objekt_nummer); Status; "Start") I've also (unsuccessfully) tried putting the calculation in the related table. What am I doing wrong here?

Untill now I've been very happy with my auto increment Client_ID. It increments by 1, from the last highest recordnumber. The fields I use are: Client_ID Right("0000" & GetAsNumber(serial); Max(Length(GetAsNumber(serial)); 4)) serial If(Get(TellingTotaalaantalRecords) = "1"; "1"; NextClientSerial) the If is needed when the first record is being created. NextClientSerial Max(Global::c_ClientID) + 1 The Global is the common 1:1 relationship This is exactly what I want. Except I just can't seem to get it to work with importing other records. I thought it worked but it doesn't (anymore). I've tried a couple things but I think th…

Hi, I am having a problem with a calculation involving an IF function. I want the IF statement to be based on whether or not a field contains a specific value. However, I am having trouble differentiating between the field containing just that value vs. that value along with other values. The field in question is based on a value list of countries. The beginning of my IF statement is: If(CountryName="Canada";" CA"). For most records the CountryName field contains many different countries, not just "Canada" alone. I want the calculation to work on any record that has "Canada" but not only "Canada". So right now it is not applying the calculati…

I would like to automate the retrival of some text in my web viewer. The problem is how to get to it. When I hit the tab on the keyboard it moves to the web viewer and then if I do a select all and copy I can get the text and dump it into a field. Is there any way to script this .

Hi all, Question 1: Is it possible to have 2 fields produce a sequential range that will then be placed into auto-generated fields to hold the results? I took a look at Dynamically Generating Number Ranges & it was close but was set to only allow numbers for entry & only a set amount of fields while I would like to use text & automatically create each field based on each text occurrence. Here is what I would like to do but have not seen done anywhere before: There is a beginning field & an ending field. You enter "a1" in the beginning field & then enter "a100" in the ending field. The phrase "a1" is not limited in any way. It co…

Hi all Can someone help a newbie out with this one?: I have a membership database where each membercard has a portal (related by a ID-No.) which shows what amount of money each member has paid over the years. I would like to have some kind of calculationfield on each membercard which shows the total of the amount paid by the member in current year. BUT! Since I'm only interested in the totals whichs exceeds the amount of 195,-, it would be nice if the calculationfield automatically deducts that amount. Please feel free to ask, if my question is a bit blurry Regards Lasse

I know you can script using a paragraph mark. What can you use to make a tab in a text formula in a calculation. Thanks

I'm a newbie. My previous data base had city state and zip in one field. I imported into FMPro7 and would like to find a way to populate 2 new fields with this info so I can search and sort. I would like to leave the city and remove the state and zip in the original field. Thanks, any ideas would be appreciated.

Hi there, Sorry for the long subject, I hope it all shows up on the forums as I haven't been here for quite a while. I was wondering if someone could possibly help me out with a slight quandary that I am having at the moment. I need to export data from Filemaker V7 as a PDF. I can lay out the data and export as PDF with no problems. The only problem that I am having is that people who are using the database are copying and pasting text into fields instead of just writing it in and so I am getting all kinds of different fonts and font sizes appearing in the fields. I saw that it is possible using Auto-Enter -> Calculated Value that I could do this …

Hello Everyone, I'd like to add together all the numbers in a field of only the related records. The thing is, I'd like to do this for multiple fields at once. I'm aware that you can do the sub-summary and sort by that field to get a count of related records, but I'd like to do that for more than one at a time. Here's a Picture of what I mean - See all the zeroes on the bottom? I'd like those to be the added up totals of what is in the portal: Is this possible with FMP 6? I also have 8 if I need to use that instead... Thanks for any info you could provide! Chris

[color:red]Instructions: If medical condition = No then Pulmonary, Cardiac, Neurologic, and Other Condition will be dynamically set to No If medical condition = Yes then Pulmonary, Cardiac, Neurologic, and Other Condition values are cleared and data entry person may enter a combination of No's and Yes's for Pulmonary, Cardiac, Neurologic, and Other Condition. I tried doing this as a calculation but had no luck. I am trying to avoid the user from clicking a button to perform the logic. I have attached the fm file and provided examples below. thank you. [color:red]Example 1 Medical Condition = No A. Pulmonary = No B. Cardiac = No C. Neurolog…

My records are made up of the fields N1 to N6. Is there any way that I can calculate how many pairs of numbers there are i.e. 11 / 12 or 34 / 35 etc One would thus be identifying consecutive numbers. . .

Ok, I have yet another calculation that is stumping me. I have a checkbox set of provinces. I have created a calculation field (with the help of the some wonderful people here) that outputs the selected provinces with a specific format, in an alphabetically ordered list separated by commas. So - for a record with Ontario, Quebec, and Manitoba selected - the calculation field for the desired output would be "Canada (Manitoba, Ontario, Quebec)." Now, I need to create a new calculation field which will take "Ontario" (when it is selected) and remove it from the list and put it at the end. To clarify, the desired output would be: "Canada (Manitoba, Quebec),…

This one's a doozy for me. I've got a table of Days, a simple Calendar where each record is a unique date. What I'm trying to do is create a calculation that returns a number based on a date's week relative to the current week. A 'week' is 7 days, Monday-Sunday ideally, but Sunday-Saturday will work. The calc field for a day in this week should return zero. A day last week should return -1, the week before, -2. A day in next week should return 1, and so on. However, next week, a day in this week should return -1. Make sense? I started off with a simple calc where _kp_Date is the date field and gCurrentDate is set by script on start up to the curre…

Hi, I'm kind of a newbie to the summary report so I need some help on this matter. I created a summary report that looks like this in preview mode: Shop Revenue ----------------------------------- Glass Shop $100 $200 -------- Total $300 ------------------------------------ Machine Shop $200 $300 -------- Total $500 ------------------------------------ Wood Shop $100 $100 ---------- Total $2…

Here is the general situation. There are two tables Project and Charges. Each cost is associated to a project by ProjectID. Each charge also has a account code ( range is from 000, 100, ... 900). Now I have to sum up by each code for each project. IOW, I need to know the total charge amounts for each one. The easy way of doing this would be to create a global calc for each account code and then I can get a sum for each one or I can create an intemediary table with 10 records; one for each code, set a global field with the ProjectID and sum it up that way. All that being said, I wanted to make my life difficult and thought that perhaps this can be done…

Hi all! I'm setting up a database that involves building projects - a lot of these will never be sold and only end up as quotes, others will likely be sold and therefore has to "go live": what I need is a basic serial number, and then a "real" serial number that will be assigned only to the building projects that were sold... The ideal way of doing it would be when the user changes a value in a drop down menu from, say "quote" to "ongoing". It'd be even nicer if it would involve a conditional custom dialog, so the user actually had to make a choice before assigning a new "real" serial number... Any ideas or suggestions highly appreciated

I would like to know if there is any way to export a filemaker database from a filemaker database and NAME the new file with the contents of a particular field. Thank You

does anyone have a clean trick to force an update on a field that is set to auto enter a calculated value. I can do an export and import, which will force the calculation but that is messy.

I'm trying to write a calculation that returns a value of 0 or 1 if a specific value has been check. The Value List has six check boxes and any combination of those can be assigned. If only one value from the value list is check everything works fine but if the value I want is check and any combinations of values is checked I don't get what I need. Any suggestion on how I can do this?

Been trying forever on this one: I have a list of products that have been delivered to different vendors in different provinces. For instance, Ontario receives 34 products in total but to various addresses. I need to be able to automatically calculate, based on the found set of records, the total number products sent to the provice. I've been trying for weeks but can't get any summary like field to work.

I have two fields (status and score) that are connected together by a case calculation. Meaning if a score is filled in of 100 the status will be O (order), is the score between 1-99 status will automatically be P (proforma). That I can get done. But when a score is zero that can mean three things; either status, M (missed), C (cancelled) or NO (No offer). Would it be possible to create a pop-up menu in which one can select one of the three statuses when a score of zero is filled in the score field? Hope someone can point me in the right direction. Thanks, Dutchy

I have a field (FullName) that reverses the order of a person’s name in another field (ReverseName). Example: Smith, Sara becomes Sara Smith This is what the calculation looks like now for the FullName field: Right (ReverseName; Length (ReverseName) - Position (ReverseName; " "; Length (ReverseName);-1)) & " " & Left (ReverseName; Position (ReverseName; " " ;1;1) - 2) This works OK, but some people have two first names (Sara Gray Smith). How can I write a calc for a Name field with two first names? Example: Smith, Sara Gray should become Sara Gray Smith TIA lpm

I can't believe this hasn't been covered before, but my searches yielded no results so here goes. I import prices from an excel file, into a number field. On the layout it is formatted to show with currency and 2 decimal places, e.g. £5.00 Is there an easy calculation that will turn '5' into '£5.00'? At present I have a cumbersome one based on case and the position of '.' in the field, but I'd love it to be simpler. I can't use custom functions, but wondered if there was something cunning I could do with the standard functions that had escaped me? (The reason for this is that the main function of the database is to export tagged text for Quark. The ex…

Greetings How does one go about counting the odd vs even numbers in a record. Each record is made up of 6 fields (N1 - N6). How can I calulate the number of odd vs even numbers in each record? Anyone have a clue? Thanks a million!

Hi gang, I am building a payroll solution and have a time sheet table. you enter a code from a job line item table and enter the qty that the employee finished. I want to validate that field so that the employee can't take more units than that line item had. I simply put a validation on it in define fields requiring that the qty not be more than the qty remaining in the line item table. The problem is that the data is being entered into a portal and until you click completely out of the portal, the validation warning doesn't pop up. A clerk could enter many many lines befor exiting and they may not even know at that point which line had too many units. Is there a way…

Hello all I've had a call from my accountant he requires a list of all outstanding invoices. I've been able to work out invoices due! Each invoice table has a portal named Payments. When client makes a payment this field/record is completed. I think i can create a script that searches PAYMENTID field but returns results if field is empty! Searching HELP now but if anyone has any advise it will be greatly appreciated! regards BFGK

Hi, I have a field called next reporting date, it is a dropdown calendar. I have the following calc in a field called overdue: If(Next_Reporting_Date < Get ( CurrentDate ) ; " Overdue" ) I have it set not to store and show as text. (based on todays date of 7/2/2007)If the reporting date 7/1/2007 the Overdue message will be displayed...if the next reporting date is 7/3/2007 the field is blank. Until you move the next reporting date up to the 14th or 15th or 16th or 17 or 19th, the Overdue message will appear, if I advance the next reporting date to the 20th the field becomes blank again. Any suggestions as to why the message shows up on t…

Hello everyone, I am compounding 2 fields (date,time) in order to create duplicate records flag. Question: By just doing a simple compound of c_flag = date&time result is eg. 6/25/20079:43:13PM would it be better (or possible) to have the calculation field hold 625200794313PM? I guess I would be looking 'DateToText' conversion. Again, this compound is to be indexed so my line of thought was that text field without "/" and ":" entries would 'index better'. Thoughts? Many thanks!

I'm trying to create a way of having a log to each record. When a record is modified, I want to keep a record of each person who modified it. (It doesn't help that I'm modifying a database that was made by someone else, so I'm a bit limited on what I can change.) I've gone through a million possibilities. Any thoughts?

Hello all I have a great invoice table thats been working great. Unfortunately, we now have a client in EU. I 've been informed that we don't charge VAT to clients based in EU. I 've created a field on my contacts list named COUNTRY with a drop down box UK or EU. If the drop down box states EU is there a way that calculation field for VAT not be used or made to = 0? Look forward to any replies! I will keep working on an answer! regards BFGK

Greetings I have 1200 records with recurring fields. I want to calculate the frequency of the recurring fields in isolated ranges i.e. from record 1 - 100, 101 - 200, 201 - 300 etc. . . Anone know how I can achieve this? Any help will be greatly appreciated!

I know how to calc totals from a portal (the portal is associated by catergory -- but when happens when I do not want a portal I just want totals shown by category. Example: I have two pages 1)Recap_Page 2) Policy_Page (The Policy page is grouped by categories (Sent, Procesing, Closed) I have a portal on the Recap_page associated by Catergory. I have totals that sum up a few fields on the policy page. I want some other categories totaled up on the recap_page with out using a portal. Can someone show me how to write an calc or the sum fuction that can group all of the like categories together and them sum a field? Thank you

Hello , All ! Does anybody know how to change lunar canlendar date to Solar Calendar date ? regards, kyle

We're building a simple ledger for our clients, many of whom pay us in installments. We can easily post charges and payments, which results in a balance. What we're trying to figure out is how to post planned installments (out into the future 18 months), then calculate whether clients are ahead or behind at any given time. This seems to involve checking whether Today <= ExpectedPaymentDate, and then calculating TotalExpectedPayments + TotalLateFees - TotalPayments. Is this a Case statement? Any ideas or references would be welcome! Thanks!

Hi all, Is there an easy way to prevent anyone from double clicking on a field with a drop down menu and adding a value not on the value list. I have reports that are based on this info and people are just typing whatever they want. Users!!! what are ya gonna do? Mike

I apologize for the brick of a question but here it is. If it is not clear enough please let me know and I will try to clarify. Thank you., What would be the best way to set up same inventory item that can be purchased from different vendors? If I have item X and that item is carried by vendor A and vendor B, the difference being that vendor A charges $1.00 and vendor B charges $1.25. I have attached a gif of my relationships. When creating an invoice, I have a portal in which I type item code in and it creates a new record in line items and pulls all of the item information related to that item such as cost and vendor code. I know how to make it …

Hello all [first post] I'm trying to create a record audit trail for all records deleted by users. I have all deletions executed through scripting, but I'm not sure how I should manage the deleted records and make restoration of them easy. Has anyone accomplished a deleted record audit trail and what methods did you implement? How can I set up this machinery to make it quick and easy to recover? Thanks for any help.

Hello everyone, It has been more than while so be gentle with my ignorance and jump from 6-8.5. Perhaps the post belongs to Import/Export section but it sort of touches both. I am working a small solution that is to import log files...essentially a single line or 'columns' separated by a space. "Space delimited" if you like. 1. Attempted ODBC I have tried ODBC import with pre-mapped fields but noticed that IPs can't be formated correctely. eg. text field: 192.168.1.115 is actually imported in format of: 192.1681 so it seems like an issue of having '3 decimal points'... Q:but it is text field, why would it care how many 'dots' are being imported? …

I have created a database to track employee time off. I use a date field for both fields, start date and end date. I have set it up to just subtract the two to get the difference. It just hit me that if someone takes time off that spans the weekend then it will count Saturday and Sunday as days off. I have been trying to figure out how to take care of the problem. Does anyone have a solution for the problem. Thanks for your help.

I need some help with a text field. I've got the following set up. textField1 is a field that can only have values from a value list, and no others. textField2 is a field that is set to "Auto-Enter Calculation" and the calculation references only textField1. It's like a Case statement, but also with some other complexities. The details aren't relevant here. However, what is important is that this field IS editable. If the user doesn't like what is in there, they can change it. Now, what I want is sort of half-and-half of the "do not replace existing value of the field, if any" property. If the user has not manually entered anything into textfield2, th…

Hello everyone, I hate to bother with this easy question but I just can’t seem to make it gooo. We purchased File Maker about a week a go for our soccer team to help keep up with stats etc. I have a layout to show the regular stuff for the players and have a pretty good grasp of how to make basic things work except this problem (this is just a regular database no joins or anything hard like that). I have a dropdown box that I made a value list for that states ACTIVE or INACTIVE for a field I call STATUS. I then made fields call ACTIVE TOTAL and INACTIVE TOTAL. And that is as far as I can go. No matter what calculation I attempt to impart I can no…

I looked but was not able to find it, if it is out there would someone point me to it or tell me how would this be accomplished? I would like a field that would count/show if a button was pressed? For example: Print button for invoices. When it is pressed it would print the invoice and insert words "printed 1" into the field next to it. If it is pressed again it would insert "Printed 2". I am looking to writing a script that once the button is pressed: Prints the invoice Checks to see if the field next to it has anything inside If it does not then it inserts Printed 1 If it has Printed # inside already it would increase the value …

hi, does anybody know the chinese calendar date to solar calendar date convertion formular ? thanks, kyle

What is the maximum length of a calculation? Not the result but the actual thing when you write the calculation - is there a limit?

hi... thanx for your support... i have a portal, in which daily status of task will be entered. ie. i have two fields date and task... i want the current date should be automatically highlighted in different background color.... how can i do this..?

Quick question... I am creating a log and wanted to know what to put in the calculation area to get the current date and time... I was initially using a field that I set to display when the record was modified, but I found this date and time to lag slightly... Any suggestions?

Is there a way to format the calculation in the calculation field when you write it? Like different colors and or visual alerts which could be useful in long calculations. Also what are the most common things that you do when writing the calculation - new rows for each line or certain order and so? Any tips would be appreciated - Here is an example of one of my long calculations: Case (Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_I ; Settings::eBay_Insertion_Fees_Tier_I_Amount ;Ebay_Sales_Price ≥ Settings::eBay_Insertion_Fees_Tier_II_A and Ebay_Sales_Price ≤ Settings::eBay_Insertion_Fees_Tier_II_B ; Settings::eBay_Insertion_Fees_Tier_II_Amount ;…

I am looking for a way to have a button or field change color if there is text in a different field? I have a button to go to a layout for comments but on the original layout you can't tell if there are any comments already... I would like the button or a flag field to change colors if there is text in the comment field without going there. (I can get a flag field to say yes or no but would rather change the color ) eye candy I guess... Thanks

I'm sure this is an easy one... just can't figure it out: I have a Contact Type field with check-boxes, fed by a value list. It's check-boxes because a contact can be assigned more than one type. This poses a problem in list view. I have the Contact Type field as a plain text field, which lists the checked values vertically (cutting off everything but the first one). How can I get it to display the values horizontally, separated by ","? Thank you so much for your input!

Hi to all.... I am a 15 days kid in file maker. (Eager to learn - Using boss machine) i am trying to use file maker to schedule different tasks for different people. I have just started to enter like this.. NAME : JOHN TASK MAINTENANCE DATE : 27 MARCH 2007 the next day john may be given a different task like DRIVING I want list out like this 27 MARCH 2007 DRIVING JOHN MARK EDWARD MAINTENANCE ROGER THOMAS VERT I have the name and task field as "TEXT" How it can be done... is it simple......? can someone guide me on this?

I would like to control data entry of dates. For example a specific date field cannot be < or > another date field or cannot be < or > the current date. I went to the define database, selected my field, then selected options, validation, specify calculation and typed the following: < table::field and but this did not work. What's wrong?

Would it be possible to make a calculation that would count how many characters has been used in a text field and to dynamically update and display the results as one types? How hard would it be? Thanks.,

I have a following calculation: "Record " & Get(RecordNumber) & " of " & Get(FoundCount) & " found " & " (" & Get(TotalRecordCount) & " total)" When I search for a particular record, I know there is only one in the file, I get the result as: Record 19 of 19 found (19 total) According to calculation above I should be getting: Record 1 of 1 found (19 total) Am I missing something?

Hello Everybody! I'm new in the community, and i'm still getting used with the calculations. But I believe my doubt is quite simple for experienced users. I have 3 fields in my database: Total Value, Comisson percentage and Comission Value I want to make a calculation, so the comisson value is automatically entered when I enter the Comisson percentage field. For example. Total Value: 1000 Comission Percentage: 20% Comission value: 200 What kind of calculation I have to do in the Comission value field? Thanks!

I want to display a field containing the average value of a field across multiple records. So the obvious thing is to use a summary field set to calculate the average. But the displayed average defaults to 7 decimal places and I would like to reduce this to 2. I don't think you can do this using a summary field (can you?) So the obvious alternative seemed to me to set up a calculation and then use the Round function. Surely this must be straightforward, but I can't work out how it can be done. Sure I can get what I want using more than one field, but is it possible to display a rounded average using just a single field?