Calculation Engine (Define Fields)

Hallo, Is there any way to keep trace of what happen in a table? some tool that can handle any change in a single database table and any event such for exemple a record modification or deletion? I'm working on a old database which has been filled with tons of scripts and procedures over the years so I really don't know how to compact them and get my work a little less harder. recently I've discovered that some procedure (and I really don't know which is) delete - appearantly - randomly some records from my db. I don't know if this procedure is called by a schedule (there are 3 terminal server users running all day) or by a human being ( : )! I have written some …

I want to be able to return to the file I left from using a script. When leaving I "Set Variable [$$filename; Value:Get(FileName)] and placed this value in a field. How do I get back that filename value?

I am working on having FM8 send an e-mail to 1 person based on information in 1 record. The problem I'm running into is a number field that is: Grand Total (a calculation field) with the calculation set to: SetPrecision ( Subtot + Tax; 2 ) it is leaving the 0's off the end if it is $163.90 then it displays $163.9 if it's $163.00 then it displays $163 ... I know that setting the field formatting to display it properly in the layout but the formatting is not copying over to the e-mail. Any suggestions on how to achieve this would be greatly appreciated. If there is a more appropriate place to post this please let me know as well, I'm new at forums and although…

Hi Guys, I have an "ItemNumber" field and some of the number & letters were entered with spaces. Like "P 335 H2" instead of "P335H2" and the spaces are not always in the same places. How can I take out all spaces? Thanks for your help, Milo

I have a fairly complex solution running over here and I recently converted it to 9 from 8.5. I began taking advantage of 9's ability to utilize external ODBC sources for several fields in the table. I have a pretty simple script that creates a new record and populates it with data. What I noticed is that when the script gets to the "New Record" step, I get a spinning beach ball for 9 seconds or so. During this pause, the CPU load spikes for almost the entire time on the host machine (little or no disk activity). The ODBC data lookups are really fast so I don't think that's it unless something is going on in the background that I don't understand. At fir…

Let me try to adequately describe this issue. I have a database for our magazine advertisers. I have created a text field of check boxes, Sept. 08, Oct. 08, Nov. 08, etc... These are the months that the advertiser's ad will run in the magazine. I have tried specifying these as dates, but it will not allow the input because they are not full dates, i.e. 05/25/2009. When I perform a search of say just Jan. 08, it also returns records that do not have Jan. 08 checked, but have Jan. 09 checked. I need to be able to search for just the records that have Jan. 08 checked and not Jan. 09. The problem is I cannot do an exclude search because some of the records I need to find…

My database tracks orders for items rented to exhibitors at tradeshows. Several of the reports I print need to be sorted by booth #. 99% of the time everything works fine using the booth # as a number field and then sorting by number. However some of my tradeshows will separate areas of booths by including a letter in the booth #. For example booths in room A are labeled A1, A2 and booths in room b are labeled B1, B2. I need to be able to sort by number and letter so that my report would end up listing A1, A2, B1, B2. With the field categorized as a number I get A1, B1, A2, B2. Which is not helpful. Similarly if the field is text it doesn't sort the #s correctly i…

I am importing a file from Excel- and need the dates to come in from Excel as 00/00/0000 - which they are this way in Excel - however when i get them into FMP - it loses this format - I need to sort by dates in my solution and it is not sorting correctly when a date field looks like 9/2/07... is there a calculation i can use to keep the formatting the same? thanks in advance

I am creating a database for an office that is cross-platform (we have about a 50/50 pc/mac ratio). Our office shares docs that are associated with specific clients (pdf, word, audio files, lots of different stuff). We'd like to be able to store these files in our database (not referenced), and have the ability to open the file from the container field, make changes, which are available for others to see after closing the document. Is this possible? I don't see how you can "open" the file from a container field.... Any help would be appreciated.

Hi, I work for an electronics reseller and I'm working on the following project: Every week we make a list of our products with our price for that week and go 'shop' our competitors to make sure that we're the lowest price around. We do this once a week. Our list contains about 400 products. We want a database that contains all this data (The product number, date, and our price and the prices from our competitors for that product on that date). Once we have this, I'm going to connect to the database via ODBC and have a php file that takes input from a sales rep on the sales floor (a text box and submit button) and grabs the data from this database and makes a…

I have a problem calculating number of days between two dates. I need to know if Date A is earlier or later than Date B. I tried the following: Case(IsValid(Date of Death and Date of Death - Date of Charge), "Warning! Patient Desceased!", "") This returns a valid result in all cases. Obviously, since most of the patients are still living, the Date of Death field is usually empty.

I have a text field with phone numbers like: 301.555.1212 vp 301.555.3232 vb I want to separate the data into two fields (voice personal and voice business), as it should have been done in the first place. I've created a second field for the business phone number, then set that field so I have the same data as what is now the personal voice field. I'm looking to build a script that will set the personal voice field so that the business phone and its suffix (" vb"), as well as the " vp" suffix are removed, leaving only the 'voice personal' number. Something along the lines of: show all records, go to the first record, loop, , repeat to next,…

Hello I am trying to separate some yellow page businesses from the internet into fields, Businessname, address, city, state, zip and Phone. the address of the sample addresses is http://www.yellowpagecity.com the problem is the addresses listed there are like this Canastota Concrete Co., Inc. MAP 306 E Walnut St Oneida NY 13421 315-363-4240 The word MAP has to be removed in every case and the big problem is sometimes it lists only the city and sometimes the full address, but there is nothing between the address and the city to differentiate the two. ( Where the address leaves off and the city starts) I could go by rightwords but sometimes…

Greetings! I am really battling with a final calculation for one of my sales reports. The report I have created is a sub-summary report to analyse total sales, GP and GP Percentage per department of products sold (i.e. hardware vs software vs. consumables etc). My report is great so far. I just cannot get the GP percentage calculation to be dynamic i.e. when I run the report over three months, my GP% calculates the entire GP Sum / Total of Recom extended. I have tried to do a sub-summary of GP Sum by Month and a sub-summary of Total of Recom extended by month and use these in a GetSummary calculation but the calculation fudges out. Does anyone know how I ca…

Would love anybody's two cents: I'm attempting to create a serial number on record creation contingent on a self-referrential relationship. I want the serial to be the numbered occurrence of records with a common match field. For example: In the db 'Referrals', I have 83 records. In the field 'Agency', 10 of the records have "HRC" and 73 have "JM". I want the _next_ record (depending on 'HRC' or 'JM' being entered in 'Agency') to auto-enter a serial number of 11 or 74. Or 1, if different letters are used. I have a self-referential relationship using 'Agency' as the match-field, but I can't seem to get the function. Thoughts? Many thanks, /c.

Hello I have to do a google search from Companies in my filemaker databases to see if they have a web site. In the formating of that search I need to generate a url within filemaker as a script step with all the words in the field with the "+" symbol between them . example "Dinosaur Bar-B-Q Catering" has to be in the url google search as "Dinosaur+Bar-B-Q+Catering" - I can understand how to do this when there are always the same amount of words in the business name but there might only be 1 and up to 6 words in a business. So in the case of one word example "Sugarland" there would be no "+" but many businesses are more than 2 words. It is always di…

Hi all I import records into a file with an auto enter serial number. Import does not trigger the auto enter function as far as I can see. I tried creating a summary field to capture the max serial number and wrote a looping script and used the set field command to set the empty serial numbers. Goto first record loop if(isempty(serialnumber) set field (sumary field; serial number) else goto next field (exit after last) endif end loop this works but this goes through all the records every time we would import records and soon are later this will get slow. I tried using the Max function, Max(serialnumber) and designated it as a global field and…

Hi Again Guys, I have a problem. I am interested in creating some custom dialog boxes that allow a user to either continue or cancel a script. For the life of me, I can't figure out how to get it to work. I've looked into the Get (Last Message Choice) area and don't see how to validate it to what a user chooses. Of course, if the user chooses "Cancel" I want the Script to Exit and if they choose "OK" the script should resume. Any ideas of how to accomplish this task? Much Appreciated. Thanks!

I work for a school district and we are having a slight problem coming up with a calculation to average our GPA's. The problem is we have some classes that are weighted at .5 and others that are weighted at 1. So how do I come with a calculation that will give one student the average of all those classes together taking their weights into consideration? For example: Class1 4.0 (1.0 weight) Class2 3.0 (1.0 weight) Class3 3.67 (1.0 weight) Class4 3.67 (1.0 weight) Class5 3.0 (1.0 weight) Class6 3.67 (1.0 weight) Class7 3.67 (.5 weight) Class8 4.0 (.5 weight) Class9 3.67 (.5 weight) Class10 4.0 (.5 weight) Together, with the weights included the final g…

Hello, This the data 0001234567 0012345678 0123456789 00123-4567 As you can see, I can't work with numeric fields cause of the '-' witch I have to keep absolutly. The question is : Is only want to keep the 12345678 and 123-4567. How can I get rid of these leading zero's ? Kind Regards

Hi guys, I'm trying to create the simple "let" function to use it in a web-viewer on my layout - any ideas, why it refuses to work? Let( $$CreationDate = Get(LayoutTableName) & "::Creation Date"; $$CreationName = Get(LayoutTableName) & "::Created By"[color:red]; "" ) It keeps telling me there are too many parameters in the function. I'm sure I will feel very silly...

How do you enter a calculation for access privileges to a record if you have more than one criteria to be able to edit that record. E.g. Must be user that "owns" the set of records and record should NOT have booked in the status? Any help with privileges whould be much appreciated. Thanks

I am having a heck of a time getting a couple of calcs to work correctly. If anyone cares to take a look at these, I would be very appreciative! This is part of a database managing chemical concentration data. One record per sample, with concentration data contained in fields. Fields I am referencing: sample type, certflag, conc, LOD, LCL, UCL 1) I am trying to flag compounds for a later calculation. This is what I want the field to do: If the sample type field contains "RM" and the field certflag contains "c" then When conc < LOD and LOD > LCL, set flag to 1 Otherwise set to 0 Else (when not 'RM' or "c") set to "". 2) I am trying t…

Hi All I am trying to get a field to calculate the total number of pages that will be printed. Example page 1 of 6 I got the first field (1)to change as the page chnages but I can not figure out how to get the next field (6) to tell how many pages that are to be printed. Thanks Lionel

Is there a way to trigger a custom dialog box if the user tries to modify data in a field? For example, date in field is 1/30/06 and the user is changing it to 11/21/07. When they begin to modify the data, can a box pop up that instructs them what they need to do before changing it?

Hi all My company has a lots of Excel spreadsheet that has hyphen separateing information in the same cell. Example: Adrian-Jude-Walker Shawn-DeRouen-Delcambre We have converted the Excel spreadsheets to a FM Database. We have created four fields : Name global field First name auto enter calculation field Middle name auto enter calculation field Last name auto enter calculation field We import the data into the global name field and would like to removed the hyphen and place the first name of each person in the first name field and do that to the middle name and the last name. I created two variables to use in the LET() function o…

Is there a way to show on a layout who the current user is that is modifying that record in a hosted environment (FM 8.5)?

Can it be set via calculation? For example: If field A = 10 then filed B = not selectable in browse mode or something like that? Thank you.,

Hi Guys, Hope you can offer some insight. I have a container field that we use to store photographs of our locations. We have a policy where photos are supposed to be resized to 640x480px, it's not a structured policy in filemaker, it's just a "We know how to do it" thing. Our users here use photoshop to resize the images and then insert them into this container field. I have two questions: 1. Can I have that container field validate in some way so we don't get photos that are over 640x480px? For example, if someone put a photo in that was 1600x1200, they would get a message stating, "This photo is too large, please resize to 640x480!" 2. Is …

I have a field that has the date entered as MM/DD/YYYY. I need to set up a new field that will convert it to text with YYYYMMDD. Can anyone help me with a calculation? Thank you so much.

Hi, I have about 60 000 records, that describe a list of incidents. Each record has an "error_type" field. It contains a string. What I would like to do is described very simply in SQL SELECT error_type, COUNT(error_type) AS error_type_count FROM table GROUP BY error_type ORDER BY error_type_count DESC Basically I want to get a count of how many incidents there has been for each type of error (each "error_type" value). What's the easiest way to do that in Filemaker ? What I have done so far is add a summary field, that I have set to be a "running count" of the field "error_type". I have also enabled the function "restart summary for each so…

I'm racking my brain trying to figure this out and coming up empty. Attached is a report similar to one my Company uses fairly often. It's a breakdown of sales by product type (Brand). Is there any way to use the SubSum calcs generated by a report? If not I guess I could always create a report from an export of the SubSum report but it feels like there should be an easier way. SubSumPercentage.zip

I have three fields: Start Date Overdue End Date What I currently have: If(Get( CurrentDate ) ≥ Start_Date + 5 ; " Overdue!" ) No problem it works and puts Overdue in the "Overdue" field…but what I need to do now is: Enter a date into the “End Date” field and for the “Overdue” field to then show the text as “OK”. I have tried a Case statement but cant make that work either? Can someone help please? Thank, James

We have a database going back 15 months, with fresh data being added almost daily. I need to get the average value of a number field for each and every month from the date the database began up to the curreht month, then feed this into 24U SimpleChart as a series of x & y values = month/year & average score for that month. I'm still trying to figure out where to start. Does anybody have any experience with anything similar? Thanks. Colin Hunter

I have a file with two related tables (A & :. In table A, I have a field titled "Start Date" that is non-repeating. In table B, I have another field "Start Date" that is repeating. The tables are related via an ID number. I would like to be able to enter the unique ID numbers from table into a repeating field of ID numbers in table B and have the related unique dates from table A automatically be entered in the corresponding repetition in the "Start Date" repeating field in table B. Right now, the last ID number entered in the ID number field in table B causes the corresponding Start Date from table A to appear in the first repetition of the field in table B. I ca…

I have a database of operator instructions that I use at my company. The database is approaching 85 MB due to the number of pictures that are stored in it. What is the best practice for a databse with a lot of pictures? I know I can store the pictures as a file reference, rather that the database itself. Is this the best method? I read a post elsewhere that mentioned creating a related databse file that stores the files. I'm not sure how big is too big, but I'm going to be adding more pictures soon and figured I address the issue before it becomes a serious problem. Thanks

Hi, I was wondering if anyone's seen a CF that can count the number of days elapsed between two dates, and only counting the days of the week that are included in a list of return-separated values. IOW 3 fields are referenced for the calc: StartDate (include in count) EndDate (include in count) DaysOfWeek (checkbox of day names) Example: StartDate = "11/26/07" EndDate = "12/3/07" DaysOfWeek = "Monday¶Wednesday" The result would be "3" because there are 3 days from the StartDate to the EndDate that are Mondays or Wednesdays or a combination thereof. Thanks in advance for any help!

Hello all! What I am trying to do is very simple, but I can't figure out how. I have this joblist, and every new job creates a new serial number (I'm using an "auto enter serial" for this field), but I need to put the year after this serial number, like this "0345/2007", and if possible, resets the count next year and so on. Is there any calculation that can be done? Thanks!

Hello, I'm trying to do this but I can't figure out how. I have this database with one Table for clients and another for partners. Insite this tables I have the fields clients_id and partners_id I wanna make now a payment table in this database, but the payments can come from the partners and from the clients. Is there any way that I can show both fields (clients_id and partners_id) in the same drop down list? Insite the Payments table? Thanks!

I am new to FMPro so apologies if these are simple questions. I have a relational database with several tables that all relate back to a contact list. I used a serial auto number for the index on the contact table. I use that number as the relationship to the other tables. I was able to setup the lookup list so that it displays the name but stores the index in a pull-down. But... I cant get it to display the name in a field on the form? Any ideas? Thanks, tonyh

I have developed a database in which the company enters all incoming and outgoing post. The problem is when two or more users enters a new postpiece at the same time (two are more users are entering a new record at the same time). The calculated serial number will be the same for users that are inserting a new record at the same time. I know the problem has to do with the used calculation, but I don't have a clue how to solve it. The database is added as attachment. versie6a.fp7.zip

I read it somewhere and can't find it anymore. A calculation to have the same outcome as the List() function in FM 8.5, but in FM 8. TIA

I have a layout upon which the user can choose from a number of different paragraphs. Whateve they pick is then in a field called "verbage". On the layout of the letter I have a merge field so <> and it shows whatever was chosen or edited into the field verbage off to the right of the page. My question is, is it possible to embed a merge field value in the field verbage and have it be treated correctly as a merge field in the letter. For example the text in verbage might be: The fee is $<>. I would want this to show as: The fee is $477.00 but instead it shows as The fee is $<> Is there a way to 'force' or 'coerce' the merge field ve…

I want to be able to calculate how many month end dates there are between any two dates. Example: Between 1/1/2007 and 3/14/2007 there are 2 month end dates. They are 1/31/2007 and 2/28/2007. Can someone help me with seemingly easy problem? Thanks in advance!

Hi everyone, I am trying to create a field that will count only the letters in another field. I have tried different formulas to count them using getastext, filtervalues patterncount and substitute but can't figure it out. Basically if field a contains an alpha character (a-z) I want just the number of those characters to show up in field b. For example: field a contains 11a24b field b would show 2 field a contains Pend12 field b would show 4 Any ideas? Thanks Mike

hello, I have what I hope is an easy question. I have a file I am importing with a date formated: Nov 20, 2007, modifying the source is not an option unfortunately. Problem is, Filemaker is not seeing that as a date, so I am not able to do searches based on date. Is there a calculation field I can create that will format my source field into a date that filemaker will recognize? or is there a better way of doing this? thanks in advanced!

I'm working on a simple conditional formating for a field, but I'm having a brain freeze. I have a field called DUEDATE. I want it to be red when it's at or past the due date. I've got that part with the "self get (current date) format. But, I have a field called APPROVALDATE. I want the DUEDATE field to return to white when "any" date is put into the APPROVALDATE field. What is the conditional format for that? I'm new and stuck. Thanks.

Hi all Im trying to get the path of my file when on a remote server ( so images will link directly to a folder and display in a container) It works great on my local machine but the calculation Substitute(GetAsText(Substitute(Get(FilePath);Get(FileName);"")&"datasheet plans/" & Generic_specificdetail::drawing rename & ".pdf");"file:/";"image:/") now returns "fmnet:/IPAddress/datasheet plans/Office_Storage.pdf" any ideas and thanks as usual

I'm using the round function to round my $ figures to nicer numbers.. After I do my exchange rate calculation, say $10cdn to usd, i get some funny number like $9.53usd for example. This isn't nice to have an e-commerce site, so what i've done in the past (when using excel i used the roundup function i think) and rounded my value to the nearest 0.25 cents.. So, the $9.53 would be $9.75 instead.. Right now I'm using ->round (calculated number goes here after exchange rate; 1). I tried all sorts instead of 1 but I didn't get the desired result.. I'd be happy even if rounded up or down to nearest 0.25, roundup isn't that important..

i want to be able to add a timestamp with the click of a button and that that to a list, this is to have a list of everytime a member came into my gym.. for example [color:orange]3/11/07 [color:green]3/13/07 [color:orange]3/15/07 [color:green]3/17/07 with a colored background.. i know i can do this with portals but i cant figure it out how.. all help is greatly appreciated

Hello Again guys, need help with this calculation/custom function please. The output I am trying to achieve in a calculation is listed below 15-40500-72000-31500-project title 16-25200-72000-46800-project title 17-25200-72000-46800-project title 18-25200-72000-46800-project title 19-25200-72000-46800-project title 20-25200-42300-17100-project title I have created a database with a start date and time, end date and time. The start date and time field produces line number 1 which is 15-40500-72000-31500-project title and the end date and time produces the last line which is: 20-25200-42300-17100-project title I need a calculati…

Hey there, i'am a complete newbe and yust one question. I use FMP as a date base where some data from my client's is stored. The date comes from an excell file and contains over 3000 records. The data is imported in a made layout. What i need to do is make an calculation wth some fields. For instance field index revenue is field (revenue 2007*10) + (field revenue 2006*5) + (field visits 2007*100)+ (field visits 2006 *50) If i make this calculation, it only calculates once. It the calculation needs te be updated with every record. Sorry for the very beginner question, but this would be the only one and any help is very much uprecieaded. Thankx! : …

I don't know whether to give too much information (include the actual calculation) or too little, so I'll assume the wonderful experts on this forum will be familiar with the specifics of the calculation I'm referring to. I'm trying to implement John Mark Osborne's RestartNumbers (from FileMaker Advisor, June/July 2006) and it's not working and I haven't been able to figure out why. The report is being generated from a table called "activeYears" and that's where I created my own RestartNumber field. I did a copy/paste of John's calculation and edited the name of the break field from his 'category' to my 'school::schoolName_short'. I've double-checked that the …

I have a set of related fields from a separate table called Harvest Dates. Users first enter their harvest dates through a portal, creating a unique related record for each date entered. Now, for summary purposes, I wish to be able to display the dates from these records in a comma delimited, yearless format, for easy viewing in a list view. for example, let's say you have the following dates entered in the portal: 10/22/2007 10/25/2007 11/1/2007 I wish to construct a calculation to display this as a single text field like: 10/22, 10/25, 11/1 I really don't mind if the last date shows the requisite year. i.e., this is okay: 10/22, 10/25, 1…

Hello again, I am having trouble with a revert dialog box that doesn't seem to want to stay out of the way. I have written a script that sets field1 to "yes". The user is not allowed to leave the layout until not isempty(field1). (or until the script sets the field.) This vield is a "not empty" field and displays a validation message if the user tries to leave the layout before the not isempty(field1). This all works great, because after the validation message shows I don't get the revert record message that typically follows, which is the way I want it. However, when I followed this exact same method with a different layout, I can't seem to make the revert mess…

Hi Guys, I have stock and a deliveries TO in my file. The product has a field called current stock, which for e.g. could be 100. The deliveries table may a several records, each with a date and number of items. What I would like to do is calculate how many items are remaining from each delivery. So for Eg. Stock Table Occurance Current Stock = 100 Delivery Occurance (4 records for example) 1) Date 1/4/2007 Number Delivered 50 [calculated field should say 50] 2) Date 1/3/2007 Number Delivered 20 [calculated field should say 20] 3) Date 1/2/2007 Number Delivered 10 [calculated field should say 10] 4) Date 1/1/2007 Number Delivered 40 [calcula…

Here's what I'm attempting: In an upgrade script that imports the user's data from a previous version, I can't figure out how to change the next serial number in the Define menu to the serial number of the last record imported plus 1, so that the next new record the user makes has the right number. Can anyone give me some ideas? Thanks a million.

Hey guys I need the attached file to perform the following: - When I type in the "Date Purchased" I need it to be validated by the range between "Date Start" and "Date End"; if it's out of range I need a message warning. - When I type in the "Date Postmarked" I need it to be validated by before the "Postmark" date (under "Date End") - *OPTIONAL* If either "Date Purchased" or "Date Postmarked" does not qualify I need "Q" to be entered as "No" automatically. And vice versa. - I need it to print only the Qualifying addresses in the "PRINT" layout if anybody can help me out with this. I really do appreciate it! validate.zip

I see that one can set field values in scripts, but how does one set the value of a container field? In particular, I have two fields ("IsRecordLocked" and "Signature"), the first is a text field and the second is a global container with a jpg of my handwritten signature. How do I get Signature to display my signature when IsRecordLocked = 1? Thanks ahead for help.

hello everyone, if i had a field that contained 11 digits, how would i write a new calculation field that would make this checksum, and create a new 12-digit number with this checksum as the last digit? this is for our UPC codes in our database. right now we are looking up the checksum manually on the internet. we'd rather have filemaker do it. I've found the formula below, but i don't know how to write it in a calculation. Thanks!!! adam "the checksum in UPC-A is standard modulo 10 calculation: 1. Add the values of the digits in positions 1, 3, 5, 7, 9, and 11. 2. Multiply this result by 3. 3. Add the values of the digits in positions 2, 4, 6, 8, and…

Hi, I'm trying to find the most elegant way to give to users the option of tagging (select) a bunch of records. For instance, let's say we have a list of several contacts and we want to isolate a few that need to be printed. The most obvious way is to include a check box on every record, have the user checking the box on the desired contacts and then do a find for checked contacts. This works great on a standalone solution, but if the solution is hosted on a server there's now a problem: User A marks his selected 4 contacts, a few seconds later User B selects a set of 5 different contacts. When user A issues the find command , he's going to find his 4 contacts + the…

I have a Photo Catalog database with 40k records (most of which are container/photo fields). The file is about 11 gigs. To cut down on hard drive space I am in the process of deleting 20k records. So far the delete script has been running for 4 days and only about 9k records have been deleted so far. Is this a normal amoount of time? Can this process be sped up?

We currently use Filemaker 8.5. We have a number of repeat customers. Instead of creating a new invoice I often duplicate a previous invoice. The problem I have is our costs on items change. Right know when I duplicate a record it duplicates with the cost of the old record and doesn't update the record with our current costs. I have to re-enter the item number of the item to get the current cost to show. Any quick fix to this?

Hi i would like to strip out the vowels from a string. Thats fairly easy but to make the resultant string fairly readable I need the first letter of any separate word to keep the vowel. eg Office: Academic = Offc: Acdmc any ideas? thanks

I have a database where the records are assigned "types", say type-a and type-b. In another table I want to have a pull down menu which will only have either the type-a names or type-b names depending on which is selected. Is there any way to do this easily? thanks

Must be a pretty dum question, but been searching for ages on this one. How do I get the current date to be inserted into a field and validated without a user being able to directly edit the field? I've done as attached example, but I'm missing the insertion of the current date into the dialog box and if the user changes the date in the dialog box and makes a date formatting error, it doesn't get an error message. How do I do this? Clone_Date.fp7.zip

Hi all, I'm sure I've seen this posted somewhere on FM Forums, but I can't locate it. I'm looking for a way to track changes/edits in a text field. IOW I want to do the following: Sample original text - I sell oranges, plums and peaches. Sample edited text - I sell oranges, [color:red]apples, plums and peaches. [color:red]I also sell pumpkins. Any help is appreciated!

I have a portal in a child table that lists charges and dates. There is a field in the parent table that lists the end of the project. How would I define a calculated field that will give me the total charges AFTER the end of project? Thanks.

List(Function)gives me a list of line items; which is great. But I need for that list to be in alpha order, instead of order of portal. Is there a calculation that will do that? I couldn't find a link. Thanks in Advance!

I'm trying to push some boundaries with container fields. I would like to use them in combination with conditional formatting to represent various parts for the kitchen cabinets I build. The goal is to have colors for these parts change based upon attributes such as thickness, etc. I need some help formatting the graphic so that the color completely fills the boundary of the container field. There is a small zone at the ends of each container field that does not completely fill with color, and this distracts from my goal. I have the container fields formatted to reduce or enlarge as necessary to fit frame. Any ideas how to make this happen? …

I am importing a text file that contains (Name) "Smith, Fred A." or "Doe, John Robert" or "Doe, John Robert Sr." or "Jones, Sam". I want to create a calc that will reformat the data to "Fred A. Smith" or "John Robert Doe" or "John Robery Doe Sr." or "Sam Jones". I tried this: RightWords (Name; 2 ) & " " & LeftWords (Name; 1) It works ok when there is a middle initial, but fails miserably with only a first/last or a first/last/suffix. How do I write a calc that uses the comma as the delmiter for the last name, and spaces for the first and middle and suffix? Thanks in advance.

I have a client that has an image database with references to image files. Some of the references are invalid, but some are valid. The images are scattered over three disks. I'd like to loop thru the records and move the image file to one directory, updating the reference. If it's an invalid path, I'd like to mark the record. Is this possible? Troi? SuperContainer?

I have a field that calculates the date into text: 1/2/2006 changes to January 2, 2006 easy enough, but there are instances where the day may not be available, generally because it was omitted from the document, so I need to be able to manually enter it as January _, 2006... I added a catch to the calc that if the left most character is an "*" then the calculation isn't activated and the "*" is automatically removed when printed.... ex: *January _, 2006 But I would rather do it differently. If I enter 1/_/2006 I get ? ?, ? is there any function that can look at the calc and see if it will fail instead of me using the "*" to force it not to calculate?

I have a table - e_mail_addresses which includes a field: data_04_opt_in - the point of this field is to store a "1" if they opt in and a "0" if they opt out and has an an auto entry calculation as follows: If(isempty(unsubscribed::__id;0;1) Data_04 is "stored" and is used in another relationship to "get a list of opted in e-mails for mailing" The other table - "unsubscribed" has a field mail_id which links to __id in e_mail_addresses Unsubscribed is populated by an import of email ids But data_04 doesn't update. I may be wrong but I was assuming that this could be done by relationship rather than a script setting the value of data_04 a…

How do you export a repeating field to excel? Which format do you use to do this? Help Thank you, Joseph

I'm not sure if this question should be in the Container Field forum or the Server Forum but here goes: I have a database for my cabinetshop that is hosted on a web server. This database is used for task management as well as training. Each task has an associated quicktime video. The videos are stored in a container field. The reason the database is online is so that several people have access to the information. Some of the people use Mac platforms, some of them use Windows. I would like to separate the video clips from the actual database in order to keep the file smaller. I would like to store these videos as a reference to the file, rather than in t…

I need to pull out the right words from a text field. The information in the field is separated by a comma ... sometimes one comma ... other times more than one. I only need the text that follows the last comma. Example 1: Mary Had A Little Lamb, cassette - sheet music (the calculated result should be: cassette - sheet music) Example 2: World Is Round, The, VHS (the calculated result should be: VHS)

There may be another post to this, I have searched and haven't found another topic which applies. Here is the nitty gritty, I bought a list of names from polk in a CSV file, I know filemaker better than excel so I opted to make the list into a database. This database contains over 2000 records. This data includes VIN, or vehicle identification number used for cars, trucks, and vans. Using simple scripts I was able to filter the list into the make of the car, but now I want to sort model. The VIN field consists of a 17 digit number in one field. The 5th character of this 17 digit number defines the model, but seeing as I only have version 5 of filemaker, I am stuck as to h…

I’d like to copy the field contents of a found set of records from Table A into one repeating field in Table B. Is there a scriptable way of accomplishing this task without the use of a text editor and lots of manual steps as described in the FMP Knowledge base? Example: Table A: 10 records in the found set – copy the Client Numbers from these 10 records into a repeating field of ONE record in Table B. There is no relationship between Table A and Table B.

Please help with calculation. I would really apprecate your help. Vocabulary_rating = Number datatype, range 1-19 Vocabulary_Rating_Status = Text If the user enters a number outside of the range for vocabulary_rating then a pop up message will prompt the user to please verify data. If the user clicks on vocabulary_rating_status to = "Missing" then vocabulary_rating will auto populate to = "-89" If the user clicks on vocabulary_rating_status to = "Not Applicable" then vocabulary_rating will auto populate to = "-99" If the user clicks on vocabulary_rating_status to = "Missing" or "Not Applicalbe" then the user will not be allowed to enter any nume…

I'd like your help with a calculation involving three fields: article (record) number, image container and image number field. If image container is NOT empty, return to the image number field the Article (record) number plus ".01" at the end of the Article (record) number. Otherwise, leave image number field blank. Thanks for your help in figuring this one out. MuthaEarth

I would like to get the value of a field from a related set of records. The value would be from a whichever record has a second field (date) which is the most recent. Wow - hope that made sense!! eg record1 - field1=35, field2=2nov2002 record2 - field1=12,field2=2jan2007 result=12 as field2 has the latest date thanks

I'm trying to figure out if i can create a formula for the following conditions: I want the condition to evaluate the UserName(AccountName) and compare it to a Field. If the AccountName matches what is in the Field, I want the Format to change...lets say text to BOLD and [color:red]RED so that when the Form is listed all of the current users records are highlighted. Do I need a calculated field in a table that Get(AccountName) so that I can Evaluate the Field with the other? Am I making any sense at all : TNKS! If ( Get ( AccountName ) & MAPPING Table::Cartographer ; "Jesse Wright" )

I have a simple small payroll DB which I have been using for a year. I originally set it up so I could enter into [color:red]"Week 1" field the hours worked & [color:red]"Week 2" field, hrs worked, with [color:red]"Total Hours" as a calc. field. I now need to track the days worked, so I have created fields [color:red]"Mon. Wk 1", [color:red]"Tue. Wk 1" etc. & [color:red]"Total Hrs. Wk 1" to calculate the weeks total hrs. Here are my problems 1. I changed the [color:red]"Week 1" field to a calculation of "Week 1= sum(Mon Wk1,Tue Wk1 etc)which caused me to lose all the previous data in this field (which it warned me it would do) [color:red]"Week 1". I ha…

What I need is for a calculated field to display a "DueDate" based on the contents of three other fields. So, if both other criteria are empty then display Field A, if Field B has data display it, and if Field C has data but not Field B, then clear the Field A data, and leave the field blank. Too convoluted? So what I need is an IF, AND, THEN(else?) function, but am not savvy enough yet to put it together. I need it to evaluate the following for my "[color:red]DueDate" field: The fields involved are: [color:red]Deadline, [color:red]FinalDeadline, and [color:red]FinalDateIn. What I need is the "[color:red]DueDate" Field to display "[color:red]Dead…

I have a report with a sub-summary. The field x is in the body and I want a field in the sub-summary that sums the values of x displayed. However, I cannot use a sub-summary field to total x, as x exists in a different table to the one the report is included in. I could use a lookup to bring x into the required table, but I need a simpler solution as this is a task for high school students who need not know about lookup fields. Any ideas?

Please how do I get this calculation to look at the last entered date and not the first? It's just a list of dates that people have attended and all I want it to show is the amount of days since last attended. Get ( CurrentDate ) - Attendance_Date Many Thanks

Hi everyone...payroll recently changed how they create employee ID (EID) numbers so this is causing a problem for one of my import scripts (we change the EID to be more streamlined) used to update employees schedules from an excel file. For example the new format always has a "00" or "01" prefix e.g. 001234 we want configured to 1234 012345 we want configured to 12345 So, here is a calculation I used to try and meet this need: Right(EID ; 5) which works fine, of course, unless the EID was originally 001234 then it goes to 01234. I know there must be a way for the calc to go futher and if a 0 appears at the beginning of this newly con…

Hi all - long time since I've been here - but I've got a fresh problem. I've developed a property database to track office and industrial buildings, their available space, etc. When I can get my hands on a photo of the building, I store the image in an images folder on the server and attach a reference to them to the building record in a container field, and I've put the container field on several report layouts I've created. The database is on our server, and I've got it passworded - I'm the administrator, and the brokers and their assistants have a different password which only allows them to search the DB. The server's Linux and running the Linux version of FileM…

Hello everyone, I have a field for zip codes. This is what I have in the calculated value: Left ( zipcode; 5) & "-" & Right ( zipcode; 4) It works fine, except for one problem. The user can add letters which I don't want. So I change the validation to strict data type: Numeric Only. The Auto-Enter Calculated Value stops working. I know calculated result must be text, but some of our users are all thumbs and WILL make mistakes. Anyone know of a work-around? Thanks.

In my Invoice file I have an Order Entry layout with a line items portal called “Inv_LineItems_InvID. This portal has a Yes/No vote field as shown below. Vote: Relationship Inv_LineItems_InvID (Line Items TO) Related to Invoices TO OrderNbr = OrderNbr. I also have the following fields that gives me the number of Yes votes, No votes and total votes. As shown below. scount: Summary = Count of Vote (Line Items TO) syes: Summary = Total of Vote (Line Items TO) cNo: Number Calc = GetSummary ( sCOUNT ; ProdId ) - GetSummary ( sYes ; ProdId ) Relationship (Inv_LineItems_Constant (Line Items TO) What I need is after a customers votes on a product they a…

Hi, I have an "upload file" script that I want my users to use to upload file references. Is there a way I can ensure that they do not uncheck the "upload as a reference" checkbox Also, is there a way to look through existing container records to determine if what is held in them are file references or actual files? thanks

Hi Guys, I'm confused by time calculations! I've looked through the forums for past posts and can find similar questions although the answers elude me. I'm working on a timeslip layout. My fields are: Date, StartTime, EndTime, HoursWorked, HoursBilled, HourlyRate and a SubTotal. Hours Worked is the difference between the EndTime and Starttime (End_Time - Start_Time). The Subtotal is HoursBilled * HourlyRate. What do I need to add to this calculation for it to return the correct result? If hours billed are 1.3, with an hourly rate of $40 the result is going to be $52, where it should be $60. Should the HoursBilled be a time field, or number field?…

Is there a function I can use to change the background of a field? I have a calculation field that I want to have "mask" other fields until the user checks a box... then I want to remove the background so the user can see the fields. ~ Chris

I am trying to calculate a specific class to a client base. For example, I have customerA, CustomerB, and CustomerC. The type of client needs to be assigned as either, Mens, Womens, or Misc. I set my Calculation to be Case(ClientName= "CustomerA"; "Mens; ClientName= "CustomerB"; "Womens" ; "Misc" This seems to work fine, but if I have many Customer names, is there an easier way then to type each one as a case. I tried stringing them together as Case(ClientName= "CustomerA" or "CustomerB" or "CustomerB" ; Mens… But this did not seem to work at all. Any suggestions or maybe a different method? Im at a loss.

Hello, I am attempting to create a calculation that allows me to replace specified occurrences of a search string. For example, say I have a field that contains a list of names: John, Sarah, Henry, John, Chris, David, John, Mary. I want to be able to substitute the second and third occurences of "John" with the text: "John(duplicate)". I hope this is clear enough. Thanks in advance!

I have clients all over the world in my FM database and need to know what time it is at their location when I call them. How can I create a field that calculates and displays their time if I know their time zone?

Has anyone come up with a way to figure the tax rate on a gross income? I made a simple one for my program that does an estimate, a very hazy estimate, and need one that is a bit more accurate. I know it won't be perfectly accurate since they change it all the time, but was just wondering.

I have a total_materials table and consumed_materials table in each of those tables there is a field called qty what would be the calculation that I need to do if every time I add a value to the materials_consumed field, the total_materials field subtracts the amount

Hi! I'm still running into problems formatting reports. I've received fantastic help here, but am stuck again. I would like some words to repeat above the first iteration of the data part on second and subsequent pages. (Also not to repeat if the page begins with eg. the trailing summary part - but that is easier.) I could figure out a way to do this if the function Get(PageNumber) actually returned the correct page number in preview mode. It doesn't. I must be doing something wrong - but what? Also - Get (FoundCount) returns the total number of records if the records were not collected via the Find function but rather by Going to Related Records. Is th…