Calculation Engine (Define Fields)

Hey all, I'm sure this has been answered a dozen times or more, but how do I create a Value List from data in a Repeating Field? Scenario: Its a repeating field of up to 10 colours, I want to then be able choose 1 of those colours from a drop down list .. but only choose from the values of repeating field of the current record, not all of the records in the database. Any tips would be most welcomed. Thanks J

Is there anything wrong in using an unstored calculation field within another calculation? I have a database that is used to print barcoded container labels. In this database, I have a quantity field and an external function that uses a calculation field to create the barcode. One of my customers requires leading zeros for the quantity field. To accommodate this requirement, I created a calculation field, qty_8_chr, that is an unstored calculation: Right ("0000000" & qty;8) The barcode calculation is Barcode(qty_8_chr,1,0,1) It seems to work properly, however, I wasn't sure if this is good practice? Any advice?

Hi, I know how to do a conditional formula based on whether a field equals an exact word or words by using if(field = "word" ; etc etc). but how do you ask if you just want to pick out 1 word from a field eg if(field [color:red]contains? "word" ; etc etc) does that make sense?

Hello, This is probably very simple, but I can't figure it out for the life of me. I am trying to make a 3 digit decimal which is less than 1 appear without the 0 to the left of the decimal point. i.e. I have an average that I want to appear as .420 and not 0.420. Any Suggestions? Thanks

I have a big problem afflicting me since 6 months which I tried to solve in differnt ways but at the end never worked properly.... I hope someone can help me, because at the moment I am complete stuck and quite desperate.... This is my nightmare: I have a database (sort of CRM) on filemaker server to which many users have access at once. The problem is, I would like that when a user go on one record, this record become inaccesible to all the other users and then, when this user leaves the record, the record become again available for all.How can this be done? Can anyone help me with some good advice please? Thank you very much for your suggestions.

I've have a table of records its the 'Many' of a One to many relationship. It includes Age and a Value. The ID comes from a Contacts Table. I've got a Summary field working perfectly except now I would like to have it only count the records with a unique ID. The values are sorted by age. I need to know how many people had a record at an age not how many records I have at that age. Example ID Age Value 223 9 10 223 9 15 276 9 12 So my summary would be 2 not 3 (3 is the current value i'm getting).

Hi all, sorry if this is posted in the wrong place. I am using a Custom Function in FM10 Adv., and when I create a new record all of the phone fields are populated immediately with the formatting of the function. For example, if I select (###) ###-#### in my preferences setup, when I create a new records I get ()- before any numbers are entered. Can anyone tell me what I am doing wrong? Info below. Thanks. Case( not IsEmpty(Format); Let( [@NumbersOnly = Filter(Phone; "0123456789"); @NewFormat = Left(Format; Length(Format) -1); @NewNumber = Left(@NumbersOnly; Length(@NumbersOnly) -1)]; Case( …

cRemaining = FT on Roll - Sum ( PaperUsage::Front_1st_RollFT_used ) and (PaperUsage::Front_2nd_RollFT_used) and (PaperUsage::Front_3rd_RollFT_used) Obviously this wont work. Im trying to get this to pull the remaining feet from all three rolls based on Roll_ID number. I think I have the relationships correct but cant get this calculation to work. Its taking the remaining feet from 3 different fields and updating one item. 3 different fields exist on one record which seems to make this complicated for me but I need reports on every specific field which is why im doing this. Is anyone able to help me with this I have been trying to get this to work for a co…

Is this possible? I have two tables Job Entry and Inventory. I select Item # from Drop down of available items in inventory. It auto populates qty avail in fields Item#1 Item#2 Item#3. Underneath each item is a field for data entry to input amount used on job for that specific item. How do i get it to update the inventory with the correct amount left. If I use a field called cRemaining for each row on Job entry page it will show the remaining amount in all three instances for each item. I need to be able to pull up that item again in Item#3 with the correct amount on the same job. Basically Im recording when an item was used in a job which is why I…

how do you define object on your own!

:D : I'm sure this has been asked many times before but I can't seem to find it. I would greatly appreciate any help you can offer, even if it's a link to another posting. Here's my delima: I am creating a database for a moving company, what I would like to do is require that we enter specific information (name, phone, email, address) before creating a file number for this client. This will allow for us to have a more sequential file number and ensure proper data entry by the receptionist. Okay so I think I need to create container fields and then a script which would then create a new record. My problem is I don't know how to do any of this. Please, any help you …

I am creating a database to track injured employees time off and have the following fields involved in a calculation: - Date Last Worked - Return to Work (Yes/No) - Date Return to Work - Time Lost (in days) The Time lost is simple to calculate if the person has returned to work, but I want it to show the running total if the person has NOT returned to work. I am thinking it should be a CASE or IF calculation, but every way I try this it comes back saying there is a problem with the calculation. I was thinking that it should be an "if, Return to WorkYN = Yes, then calculate using the Return to Work and Date Last Worked fields and if, Return to work = …

im currently remaking my companies database from 5.5 to 9 adv. This hasnt been too easy because alot of the calculations that were used in 5.5 arent available in fmp 9. I was able to figure out most of them, but "Status( currentfieldcontents)=..." was one of those that i was unable to crack. Does anyone know what i can use in place of this? It was part of a script in my 5.5 DB. It looked something like this: If[status( CurrentFieldContents)=Date Lookup] Go to Next Field Copy[select] and etc. Any help would be greatly appreciated

Hi All, I have an ingredient quantity field in my recipe database in which I typed numbers as I wanted to see them, ie all quantities less than 1 were prefixed by "0.", and non-whole quantities above "0" used either 1 or 2 decimal places, for instance "0.25", 0.33, 0.5, 0.75 - this is the easiest on the eye for my application. However, because I've now made the field a calculation field (so I can expand or reduce the number of serves), Filemaker wants to give me 2 decimal places everywhere. Does anyone know how to fix this? Thanks in advance for any help! Helen

Hi All, I have the standard calculation in my database for a phone number: Let ( //define variables: [ rawNumber = Filter (Phone; "0123456789") ; length = Length (rawNumber); red = RGB (160;0;0); //set error flag for a phone number that's too short error = If ( length < 10 ; TextColor ("error: " & Phone; red); "") ]; // now apply the phone formatting and return resuilts If ( error ≠ ""; error; "(" & Left (rawNumber; 3) & ") " & Middle ( rawNumber; 4; 3) & "-" & Middle ( rawNumber; 7; 4) & // this condition tests for extra digits that we'll treat as an extensio…

FMP 10 has the feature to save finds. That's great. It also offers to insert "operators". also great. One operator I'm missing is the "or" operator. Of course I can search, let's say for the name Smith first, then I do another search for the name Johnson and choose "extend found set". But I can't save such a found set. Or am I wrong? In my existing version I built in a very quick and very dirty solution, which is copying and saving the IDs of all found records. When I heard about the "Save Finds" option in FMP10 I was so happy. But now I found that it still can't save "OR" requests. Some idea?

I have done this before, but it was a long time ago. I should know this, but being a beginner really bites. Anyway--I want to use a calculation in a field to grab information from another field. The information I need is ALWAYS between--- and of course that situation is found only one in each record. [color:green]Example of text--- B. Rudman, Esq. M. Mayhew S. Kimmel, Esq. [color:red]What I need is just the names-- B. Rudman, Esq. M. Mayhew S. Kimmel, Esq. [color:red]What would be the calculation I would use to achive this?

[color:red]How do I get the month to show as 01 vs 1? Thanks for you help

As I step out in using calculations some help would be appreciated. I am trying to do a calculation that will multiply the course number by a set fee, i.e. course number * 350 for example. However, and here I get stumped, the fee is a variable depending on a certain field called scholarship, which has a number of options like "ETC" or "EKHC". I have got the following calculation to work, using IF, so that when "ETC" appears in the scholarship field it will return a result of course number * 350. course name * If (Scholarship = "ETC"; "350" ; "260" ) This works great! But what I am wondering is how I can add additional IF qualifiers to the same calc…

Hello everyone. I'm glad I found this forum as I certainly need some help. Here's my first problem. I hope I can explain myself clearly as french is my first language but please don't hesitate to ask for details. I'm building a database that look like a sale order. First table is a list of items. Second table that is a line item. Third table this is called doors and windows. I have a portal in that last one with choices of doors and windows that works with the line item. SO far nothing special. Forth table have a few fields that include data for extra cost ex: field: color_garage_door10x7 $210 Now what I want to do is add a field in my third tabl…

Is it possible to define a calculation field based on a current layout?

How do I compute a child's age in months? I have two DATE fields to compute the age from, child_dob and school_date both are 4-digit dates. I am not sure if I should use a Calculation field or scripting logic. Thanks in advance for advice.

hi, we have a problem with a summary field in that it recalculates the total in a related file, the field is not displayed in this related file, and none of the fields used in the summary/calculation (for Points_s) are being updated... the user is then forced to wait while the Points_s field is recalculated. this is problematic when the file in which these fields is stored contains ~50,000 records. in general the Points_s field is used in reporting to determine how many points a person has accumulated based on various flags. details of the field are as follows: -- Points_s = Summary field, Total of Points_c -- -- Points_c = Calculation …

I have a. Job bd for The work I am doing b. services bd for the services I do c. parts bd for the parts I have (order comming, in, out) how on the job bd can I on this I do will have the services and parts in for same line and duel relationship

Hello... I have no idea where this fit in the discussion groups, so perhaps I'm posting in the wrong place. I have a field that has 8 as storage. I want to auto-enter in the 8th storage place, but in the auto-enter options, I can't specify field[n], so it constantly enters in the first storage place. any ideas as to how to do this? thanks!

I have a fairly large Let/Case calculation but I've been asked to change it slightly. I hope it doesn't get broken by this. I've been asked to evaluate if Field A is +/- 2 of Field B. I've never written any calc using this concept first can it be done, can I just add it to the case without adding fields or do think I need a field? Sorry if this is not explained well.

Summary is giving a different figure to the figures on the list of records selected. Example Record 1 = 200.15 (actual 200.153) Record 2 = 100.75 (actual 100.754) Total = 300.90 but Summary gives 300.91 as it is picking up the 3rd decimal place to make up the extra 0.01. How do I make Summary ignore the 3rd decimal place? Any suggestions appreciated. Keith

I am used to using Nested "If" statements in calculated fields, but wonder if there are times when I should use a "Case" statement instead? Is there any general advice as to when to use which, and what are the advantages and disadvantages of each.

I am trying to count the number of records in a table but I don't want to count any record that is blank or contains a zero. Any thoughts- thanks from a FMP newbie!

Hi, I have a table that is a list of members of organizations. Each record is a unique member but some are from the same organization. I want to count the number of users per organization. My optimal solution would be to have a field with a total number of entries from that org. Can I do that with FM? In excel I use the 'count if' function. Thanks, Tom

Hello, I'm trying to create a database for issue tracking and i can't seem to get subtotals to end up the way i want them too. I'm trying to create lists/records associated with a specific number but it ends up adding them together. example: I have 4 & 5. 4 has 3 journals w/ decimal-time associated with it, 5 has 2 with decimal-time on it. Instead of grouping times for 4's together it subtotals everything together. sql statement should look some what like this select id#, sum(amountoftime), date from table workdone where id# = "4" I believe FMP uses C#, but my C# is newb to non-existent. Sorry if this doesn't make sense, but i'm pr…

Not sure of the correct way to do this... Ideally I'd like to have a single field in which I enter text and if I don't enter any text (i.e., it's left empty) it reads "not applicable." I won't even try to describe the ways I've failed... can someone give me some ideas on how to succeed? Please and thanks.

I need to figure out how to print a report giving all the employee hours for the entire week. The list needs to show each employee for the dates of 2/9/09...2/15/09. So far i can only get the employee name for the the day not the entire week. I need it to read like this 2/9/09 Joe 8.5 2/10/09 Joe 7.5 2/11/09 Joe 8 2/12/09 Joe 8 2/13..... 2/14..... For what ever reason i cannot get the employees to list in this manner is it or is it not a find/sort issue or can i or do i have to write a script for it? This is so confusing!I've tried to look up scripting on here as well but i cant get it to do what i need. Any ideas/help would be awesome!

I am a beginner in FMP. Here's what I need. If Day of week = Sunday through Thursday, 1 If Day of Week = Saturday, 2 If Day of week = Friday, 3 1, 2, 3 simply represent the result. I just don't know how to write this calc. Any help would be greatly appreciated. Maui

At least I think it is a calculation problem. All following figures are approx. File of 200 records. A record is a job sheet which is active for up to 3 days. It is then retained to provide ongoing analysis, i.e. records are never deleted. Five new records are created each weekday. Fields relevant to my problem are: 1 Service (Supplier). This contains a value list of 10 Services (unlikely to increase above 20) in the form of 01 ABC, 02 XYZ, etc. 2 Value. This contains a value list of 5 numbers (unlikely to increase above 20) in the form of 0.50, 1.00, etc. Each Service is allocated a particular value which is reviewed at week end and which may…

I've been poking around for a custom function or a calculation that does in v. 8.0 what list() function does in 8.5 and higher: I have a table of users, and a table of donations they have made, often multiple donations in a year. I want a single field which can show every year they have donated (then I'll have to eliminate duplicates and extract gaps, but that's another matter). I'm sure this has been done many times before, but I haven't found this particular case searching the forums. Thanks for any help.

Hi I have a calculation field which produces the result I want. The thing is that when I click on the field in Browse Mode I can see the text that has been generated by the calculation so I know it's working correctly, yet when I click away and commit the record the field displays a number. Please see attached screenshots so you can see what I mean. Anybody got any ideas what could be causing this? I'm stumped. Before: After:

Greetings, I am creating a database hosted on FM Server to be accessed using IWP. One of the design requirements is to be able to log either the machine name or IP address of the client machine. Is there a way to accomplish this? The only solution I've been able to find involves PHP, but I am hoping there's a simpler alternative. Many thanks!

I'm hoping someone can point me in the right direction. I'm relatively new to FM and am having trouble with this task. I've tried searching but maybe I'm not using the correct criteria. I've volunteered to set up a database to track all the results for a 4H dog clubs event results. I have a number of tables that are all working great except when I try to determine the kids placement in their class as a calculated un-stored field. This same task is simple to do in a list layout by grouping and sorting but I would really like to have their placement (1st, 2nd, 3rd etc.) show up in a calculated field that I can see and print out on labels etc. It becomes quite difficul…

I am having difficulty finding a function that will allow me to combine the contents of two fields from two differnt records. Record 1 and 2 both contain a text field Game_Results. Record 1 Game_results contains "Moose 1 Hellmovers 2" while Record 2 Game_results contain "Pushers 2 Condors 6" I need the result to equal "Moose 1 Hellmovers 2, Pushers 2 Condors 6" Is there a function that will do this?

Hello - I've read previous posts about similar topics, but could not find a solution. Did I miss it? Could anybody please point me in the right direction? I created a database to organize about 2000 PDF files. All the PDF files are one page long, letter-size documents. The main layout has a container field for a PDF to be inserted as an object. The field is big enough to show the PDF at approx. 80% of its original size. Currently, I have 75 records in the database. The size of the folder that has all the PDFs is 1.69MB. The database file is 340MB. That’s a ratio of 1:186! I have experimented with Super Container. But on Windows X…

Hi, I have a problem with sorting a table. I have to sort an adress list according to the norwegian Postal code. that is a code that goes from 0000 to 9999. The problem is that the norwegian post wants it to be sorted, but not logically! The codes go from 0000-1499, then to 1700-1799 and then back to 1500-1599 etc. I know about the custom value list, but that takes "only" 3.000 entries. I obviously need 10.000 entries. Is there a way i can solve this? Thanks in advance, Peter from Norway.

Hi, I've got a list that describes all the people who have a picture PEOPLE::THEIR_PICS = List (PICTURES::IDPicture) Which returns something like: 1 3 5 44 361 etc I'm trying to isolate the nth item, so that i can use that value in a relationship's portal, ie PEOPLE::View_Pics = 5 (3rd value) and then in the relationship: PEOPLE::IDPerson = PICTURE::IDPerson AND PEOPLE::View_Pics >= PICTURE::IDPicture which then shows pictures from the 3rd value upwards (if PEOPLE::View_Pics = 5) The reason that i want to do this is that i'm creating a horizontal portal made out of multiple portals that start at row numbers 1, 4…

Hi all, I'm making a filemaker database that will contain lots of pictures (currently 3000 jpegs weighing about 400kb each, and about 1gb of video files and expanding) - the database is to be opened remotely by multiple clients and storing the files as refences is proving to be a nightmare. I'm estimating the database will weigh around 13-16 gb - is this realistic, ie will it slow down? does anyone have experience of using such a large filemaker file? is there a maximum filemaker can hold? any help very much appreciated, cheers edit: it seems the max size is 8terabytes, so theoretically 13gb should be okay, so i guess the issue would be hardware? anybo…

I hope this is in the correct forum. I am new to FM Pro. I am building a relational database, and in one layout I have a portal showing the document records related to a client ID number. The portal can show up to ten document records, and each document record in the portal begins with a field showing the unique document ID number. I would like to define a script where, in browse mode, the user clicks on the document ID field in the portal and the script finds that record number. I presume that I can use an 'OnObjectEnter' script trigger for this, but cannot work out how the script can read the unique ID number contained in the field that is being…

Hi All. Hope the New year is treating you well!!! I would like some advice on how to break this calc down. This repeating field calc is used to display a calendar. I have buttons over top (result gives 40 days) with a script parameter from 1 to 40. What I want to do is select the date in a return delimited fashion into another field. I can't seem to grasp the math/parameters in this calc. Is there anyway you can break it apart for me? Thanks Mike Let( [ dateExtended = Extend( Login_Today ); // Change the field references in the Extend function monthDay1 = Date ( Month( dateExtended ) ; 1 ; Year( dateExtended ) ); day0 = monthDa…

I need to create a calculation to that will insert a serial number on demand (ie I want sequential serial numbers, but I don't want one generated for every record (sometimes I want to be able to enter a serial number by hand for legacy records) I need help coming up with a calculation to be performed on a button click but have had trouble (I am trying to use the Max command). Basically I want, whenever I click on this button the serial number to be set to the highest number in that column + 1 Any help would be greatly appreciated. Thanks

Ok this one has been puzzling me. I know its possible but cant get it to work. I have two tables Inventory and Usage I input inventory paper roll per record. In that record has all the info i need on each roll. Lot number, FT on Roll, Date Received, etc. On the field in the Usage Table where the Data Entry User inputs amount of ft used I want it to update the inventory. So say the roll has 1000 ft and the user used 100ft of the roll. I want for that specific roll number to have its ft on roll updated to show that 1000 minus what was used. Also in the usage table. I want the user to select the roll number from a drop down of which will show rolls…

Can I set up filemaker to email an alert if a date has expired? Not sure what area this should be posted. I just upgraded to fm10 and fms10, so perhaps it can be done with one of the new features. Thanks

Long story made short: What would be the equivalent Excel calculation for the following FileMaker calculation? Case(IsEmpty(FieldA);X*Y;C*D) Thanks!

Hi, Am trying to set up a database that stores reports on a OSX server. The database will be shared using FMS9 and be accessed by clients using either XP or OSX. Have been trying to find ways of displaying the pdf files on the webviewer but to no avail thus far. Any thoughts to a man slowly going crazy on this subject!! Thanks!

I am trying to create a method of vehicle damage notification and wish to be able to draw a circle or square over a fixed drawing of a car side/front/back etc. I have some thoughts, but am pretty stumped, if it is possible at all. Any help would be much appreciated :qwery:

I have a FMP6...yes I know outdated, but... have global fields that contain starting and finishing dates dates used for inserting a school term date in another field. In 2008 I changed the gloabl field dates to 2008 dates and the DB worked fine. This year I changed the dates in the global fields but if I exit the DB and return the dates revert to the 2008 dates. What am I doing worng?

Sorry if a repost. Want to subtract a date field from the current date to get the age of an item. It's escaping me today, it's Monday Thanks for the help.

I have a summary field that is summing a field called 'count.' The 'count' field is counting the number of records in a different table based on a relationship. Thus the values in the 'count' field changes based on the criteria set. The 'count' field updates properly, but the summary field is not updating when the criteria is changed. The summary field is located in a TGS part. Any help would be much appreciated as to what I may be doing wrong. Thanks

I have a db, with three tables. Two of the tables have data in them that get used by two different departments. I have the third table to give me a summary of the two other tables. The issue I have is that the first two tables have a notes field that I want to merge in the third. I know how to add characters or spaces to separate things, but in this case I want to add a carriage return. How do I go about that.

Hi all, I'm throwing the towel in and asking for help on this one. I'd like to show the percentage of sales goals in a subsummary report. Here's my current setup: The report shown is based on the table Projects. The subsummary fields under the headers New Quotes and New Orders are based on Projects as well. The number I want to come up with in Goal Achieved is the result of the New Quotes value of a given quarter divided by the value in Budgeted Quarterly Sales Goal (shown in header), then that result quotient multiplied by 100. In the case of the example it should be 103% for Q-2 2007 The value in Budgeted Quarterly Sales Goal comes from a related …

Dear FM talents, I have a challenging problem. In a table with: - students (indexed ID related to other table) - dates (date) - lesson start (time) - lesson end (time) I'd like to prevent making an new record with a lesson that starts or finishes while another lesson takes place. I've made a self-join and a calculated field (with patterncount(list(etc.);dates)) to identify the lessons that take place on the same day, but I don't know how to do it with the times. It only works when two lessons start or finish at exactly the same time, but I want to identify the lessons that have a time overlap. Any ideas? Thank you! EB

I have set up a database to keep track of my workouts. I have a calculation field (( reps*weight)+(rep1*weight1) etc..) that determines the total weight lifted and is part of that record. The goal I want to acheieve is to be able to reference this field from a prior record to comapre it to the current record and calculate a percent change. The problem is I don't know how to write a calculation that references that calculation field for a specific record. Any suggestions are apprechated. Thanks Ben

I was wondering how I could create a calculation that references data from a field but from a different record. So if I had a field "weight", I would like to create a calculation that would show the Delta between the current amount and the previous records weight data. So if weight were 10 in record 1 and 20 in record 2.. I need to understand how to tell FMP that I want to operate on different records. The expression would look something like this: Weight (current record) - Weight (Current record -1)= How do I express this in FMP?

I have a db with thousands of QT films on my machine & is well. So I put the fm file & the folder with the films into a FMSA9.03 server. Surprisingly, all my containers are empty. So now I remember that FM uses full HD addresses to locate files and, naturally, the FMSA has a very different server structure. So the question is very simple: is there a way to calculate a container QT address based on saying that "all the files FM is looking for are in this folder or a subfolder" or otherwise to amputate a part of the old address and replace with a new address? Many thanks

Hello all, I have a layout in my database that I am trying to use as an order entry form. When the form is complete, I'd like the database to send out an email with the body of the email being a list of field names and contents of the record displayed as a list. Like this: Entry Date: January 20, 2009 Customer Name: 3M etc. I've been trying this using the FM email feature, but it only lets you include the contents of one field. So I was thinking if there is a calculation that I could use to list the info as formatted above, in one field and then include that in the body of the email, that would solve the problem. Does this sound lo…

Simple situation. I have let's say 1000 records numbered A1 to A100. When I am viewing a record, I want a simple way to move to another record, e.g. I am on A25 and wish to go to A103. What I would like, unless there is something better, is to enter, let's say A50, into a Find Field and press a Button and go to A50. Thanks Keith

Hello I am trying to parce data from a web site. There is always a number value immediately followed by a period. I am trying to delete that data and leave the rest. ex. [color:red] 12. the cardinal ( I need) the cardinal [color:red]156. the baseball ( I need) the baseball There is always at least 2 but up to 5 numbers followed by a period. I am just trying to find a calculation to get rid of the number value and the period but it is not always the same number of digits. I could use the help - Thanks

how do you make Data Structures in filemaker!! I did this in FM 1 and 2! I forgot what I did!! thank for you all of your's time

I need to be able to add a leading zero to a number ONLY if the number is a single digit ex. 1 needs to be 01 but 12 can stay as is. Any ideas out there?

Hopefully very simple for you FM Experts and thanks in advance. Mac OS X and FM9 Setting up Calc field If Field A = Buy Field Y - Field Z If Field A = Sell Field Z - Field Y If Field A = " " (empty) Field X Content The entry is always a figure. Got confused as If Function seems to give two results. Thanks again Keith

I'm trying to avoid have 4 container fields, each with a different color, and then have the calculated container field select one of those containers depending on certain conditions. This example below doesn't work, but I'm wondering if I can avoid having the additional container fields: Case ( Item Type = "film"; RGB ( 213 ; 237 ; 245 ); Item Type = "digital"; RGB ( 207 ; 253 ; 188 ); Item Type = "silver"; RGB ( 230 ; 230 ; 230 ); Item Type = "mounting"; RGB ( 230 ; 206 ; 210 ); Item Type = "signout"; RGB ( 255; 255 ; 205 ); "" ) Thanks, Michael

Hi I have a portal on a layout that has a dollar amount field and a "cost center" field in it among others. I would like to include another field that sums the dollar amount field for all records that have the same cost center value. I did this previously using a portal sort equal to the cost center field, a summary field for each record, and used a GetSummary() function that had a breakField equal to the cost center field. However, this no longer functions because I had to change the portal to reflect a second instance of the underlying table. Does anyone know of another way to achieve this? John

Hi experts, I'm a long-time user of FMPro, but untrained and dangerous! Though English, I live in Spain, working in Windows XP Pro (in English) and FMPro 6.0 (in Spanish). I'm developing a (for me) complex sales and invoicing solution for a friend and decided to adapt some ISO starter files from the Scriptology book/CD I bought some years ago. Everything seems to work fine, except that I cannot format the date fields to display (month names) in Spanish, as the user wants on his invoices. The options only allow for English. I've tried toggling things like Use system formats, but to no avail. The Default language for indexing and sorting is greyed-out for some reason, so …

HI I created a Job Record Sheet and using the Auto enter Serial Number for my Serial Number on every Record. I set the Auto Serial Number as 2009/0001, so the next Job Record sheet 2009/0002 and so on. I had no problem on this. The problem here is that sometime, in one Job we have differ edition and we need to have the same Job No with a Alphabet add to the back of the Job No. eg Job Record Sheet 2009/0002 for one edition, Job Record Sheet 2009/0002A for another edition and so on. Is they is a new Job Record the Job no start 2009/0003 Have anyone know how to sole this ? or any other way I can do it. Thanks gregory

Making should that a entry is not enter in the field in all db yet! In a function! Thanks for all your time

Can someone please explain how these work and what triggers their recalculation.. and why you would use them because I for the life of me can't work it out.

I have created a date calculation field and want it to automatically calculate two years minus a day from another field: i.e. Date: 25/12/2008 Calculation: 24/12/2010 If I add 730 days to the first field I sometimes get the right calculation but not always. I suspect leap years come into the equation but I'm not sure how to deal with this. Any help would be greatly appreciated.

I like to get a random string alpha-numeric that is not use in the db yet! this is for the index or references for entry!

It must be getting late in the day and my brain can't seem to make sense of the possibilities! Anyways, I'm trying to calculate average per hour. Each call log has a time stamp. There are three fields to calculate total time, one for min and one for max one showing the total time spent for calls. Another field summary totaling calls. These all work fine. How do i get calls per hour using the total call time? Does there need to be a conversion of time to number to perform this calculation? Regards, Mike.

I have two forms I use. Job Description Job Info I create a new post with Job ID J09001 and on the JI page i can select the already jobs entered on JD page from a drop down and enter in the info per job. Two questions. Is there a way to eliminate the job numbers in the drop down on the JI page that have already had info entered. By the end of the year with two min jobs per day that drop down is going to be huge. Also can i restrict editing to already inputed job info on the JI pages? I want to make sure my employees are not messing up data already entered which will throw off reports when generated. Thanks your help hope this makes s…

Hi all, I have 20 fields each of which are a calculation The majority of the time the calculation result will be a number At times it could be N/A And at other times it could contain a symbol e.g. <0 The problem is on my layout I would like to format the fields so they are set to 1 decimal place, a comma seperator is used and negatives show in brackets If I make the calculation result is...NUMBER Then I can certainly set the decimal points, the seperator and the negative brackets no problem, but <0 is formatted as 0.0 and N/A shows as a blank field If I make the calculation result text, the numbers come through, the N/A shows correctly…

I have combined several fields (Surname, First Name, Cell, email) into one field in a database. I take the associated Company Name from a different database file and incorporate it into this 'composite' field giving me (Company, Surname, First Name, Cell, email), works fine. I want to use this composite field in another database file as a drop down menu (and use lookups to fill in the fields in this file). However I cannot do this because when I try to setup the drop down menu referred to above I get the message "This Value list will not work because the field.......cannot be indexed. Proceed anyway?". Any help would be appreciated, Thanks. Heraldo

In a portal, I want to have an icon display only if there's a related record for that portal row. (i.e. the last row won't have an icon) I set up a global container field with the icon in it ("iconfield"). This is in the relation that shows in the portal. I set up a calculation field that tests for the primary key of the relation: If ( IsEmpty ( kp_key );""; iconfield ) This is also in the relation that shows in the portal. I know the logic is right, because this works as expected: If (IsEmpty(kp_key);"empty";"full") I know the icon is there, because if I simply put the iconfield in the layout, it shows up. But using my calculation, where th…

Hi, I have a db that has about 10 records in it. Each record has a field for "x" but not every record has a value in said field. I can't figure out how to write a calculation that will give me an average of the values that I do have. For example say out of the 10 records 6 have a numeric value in field "x" and the other 4 have yet to be entered. How can I get the average of just those 6 records keeping in mind that the other 4 values will be added at a later date and therefore will be part of the calculation in the future? Thanks

Hello, I'm sure someone else has already figured this out and it would save me the time. In a calculation that could result in a fractional return, I want to round down to the lower integer even if it meets the standard for rounding up (0.5 and above). Your ideas? Thanks for taking the time to read and respond!

Hey all, I'm making a filemaker database that will contain lots of pictures (currently 3000 jpegs weighing about 400kb each, and about 1gb of video files and expanding) i'm imagining storing them within the database will make it slow and unweildy so i've decided yto store the pictures as refernces. the database is to be used by about 5-10 people and is being stored on a mac running tiger, and opened remotely from clients on other macs running a mix of tiger and leopard. the images are stored on a firewire drive that is connected directly to the computer hosating the database. the problem i'm having is that once the images are loaded into the container fiel…

Hello All I have a text field called "bigtextfield" which contains many lines of "tagged text" items of which the following are just three: <>Charlie Anylength <>[email protected] <>sometext I have a calculation field of the following format, which successfully does what I want, which is to "parse" the contents of the above field splitting the text of each <> into a separate field: Let ( [ start = Position ( bigtextfield ; "<>" ; 1 ; 1 ) + Length ( "<>" ) ; end = Position ( bigtextfield ; "¶" ; start ; 1 ) ] ; Middle ( bigtextfield ; start ; end - start ) ) However, it fails for the final line of "big…

I am trying to get the correct date calculation for my database. I have a creation date , days allowed ( example 10 days ) and I need the new date field. I am using date created + 10 = new date field. Some of the dates are correct, some are wrong. What I am doing wrong ?

How do I search and replace hard line breaks in a field. I can't copy paste them... Is there a special character? Thx Lars

Hi - I'm working on an FM9 DB which has 2 layouts for most tables - one in English and the other in Welsh. Each table records both the English and Welsh field contents (Town_English, Town_Welsh, etc) For example, a Welsh user views the Welsh layout and corresponding Welsh fields, whereas an English user would see the English layout and only the English fields. My problem is that since this is a multi-cultural DB, if one user changes the Welsh Town field (for example), the DB needs to change the English Town field accordingly (ie the Welsh name for the Town is entered, and a case statement calculates what the English equivalent is and changes the English Town fie…

In a multi-key field which is delimited with hard returns we have a number of lines with only the hard return because of an import process. I want to run a calculated fill down to strip out the lines that have only the delimiter and no actual data. I was thinking I'd use the substitute function to substitute the hard return with "", but I'm not sure how to get just the empty lines.

I thought this would be simple, but, it's driving me crazy! Here's what I'm trying to do. I have a text field fk_UnitID that contains the following type data. I-02F or II-04G or I-12A, etc. The I or II denotes the Building and the right side the apartment. I set up a calculation to get the Building from the UnitID: Case( Left(FK_UnitID;2) = "-";Left(FK_UnitID;1); Left(FK_UnitID;2) ) My problem is I get the II if the unit is II-02F but get, I- if the unit is I-12A. I just want I. What am I doing wrong?: TIA Al

Hello! I have a calculation field that has 15 repetitions and only the first repeating field is getting calculated. Here is the situation : I have a number field in another FM database that has 15 repetitions each with a number in them. I want my calculation field to refer to that field and do a simple math equation for each repetition in my calculation field. I've created the proper relationship, and I know that works because the calculation I get in my first repeating field is correct. It just isn't calculation for the rest of the repetitions (It displays 0). Here is my simple equation : (EXPLOITATIONS_ADMIN_ForCostLocal::ExploitationCost) * (DaysSkuad# /…

Hi there, I need some help with this problem, please: We have several tables with partially migrated (old) data and new data. Whenever a user navigates to a list of records (based on one table), there should be a display of statistics showing how many records are migrated, and how many are new ones. Also for search results. If I use statistic fields this slows down db performance very much - up to 50 users simultaniously, statistic function needs to run with every user accessing a list layout. Is there any quicker way to do this (selfjoin relation, portals?)? Thank you.

Hello everyone I'd like to implement a function in Filemaker Advanced 10 would calculate the Levenshtein distance between 2 words ( the Levenshtein distance is a metric for measuring the amount of difference between two sequences). For example, the distance between "Madonna" and "Modanna" is 2. There is a good article on wikipedia: http://en.wikipedia.org/wiki/Levenshtein_distance Unfortunately, I didn't find this feature in BrianDunning and others :-( If anyone has a solution, I'm interested :-)

Hello friends so i went threw a couple of pages of old topics and didnt see anything pertaining to this. i have a price field that is formatted as currency ($) with two decimals what i am trying to do is have it so that as numbers are entered they would start the the right of the decimal and move one by one to the left instead of starting to the left of the decimal EX. if i were to enter 550 currently it would show as $550.00 instead i would like it to be $5.50 any help would be much obliged or even just a push in the right direction of how i would do this would be helpful

I am not sure if this is the right use of a calculation or not, but it would be handy if it is. I have a table which is an expenses form, that has 3 key fields, an amount, a value list that includes a list of types of expenses & a project ID that is associated to the expense. I also have a layout that lists by field a sum for each type in the value list (ie. the value list has food, so the summary has a field called food) In short, I am trying to make a summary page that adds up the expenses associated to a type of expense for a project. The calc would be held in the summary layout field and would do the following (in laymans english): Add up all the amounts w…

Is there a way to change a date format from MM/DD/CCYY to CCYYMMDD? I assume there is a way to do this with XML. I dont know XML very well at all, is there a primer somewhere on using XML stylesheets? Thank you!

Greetings, First let me thank you in advance for assistance with my question. I have been using FMP for a while, but not doing any calculations. I am working on a DB to calculate the cost of a laborer. The laborer gets paid at regular time, time and a half and double time. I would like the DB to be able to determine how many hours are regular time, time and a half and double time based on the time the person starts and ends working. I have been able to get the "total hours" field to calculate the hours worked based on a time out field - time in field calculation. It displays the hours worked in HH:MM:SS time. I would prefer it to not display the :…

In one of my solutions, I came across an error occurring in some of my calculation fields. I thought they must have been caused by something I was doing wrong. However, as a test I created a sample db just to see if I could easily replicate the problem, and it turns out I can. This is an extremely simple calculation, but the result returned by FM is not 100% accurate. That is to say, the result is accurate to maybe 16 decimal places, but that's not quite the same as being 100% accurate. Here's how I replicate the situation: A number field called Value1 A number field called Value2 A calc field called Result, = ( 1 / Value1 ) * Value2 Now, if you …

Trying to create identical field layout and fields with there respective calculations etc. to another table without mixing the actual data or do I have to re input the fields for each table layout i create? Im trying to seperate records when i do my reports and dont want everything in the same report. If i use the fields from the table already set up on a new layout will that create a new set of records or will they mix? I can print reports from specific layout right?

Howdy, all: I have about 400 records where each text field begins with a sequential number and has a following instruction, like so: 003. Check, at the aircraft, that it carries an appropriate current airworthiness certificate. 004. Check, at the aircraft, (if required by procedures) for a current and approved Flight Manual for that type of aircraft being operated. ...etc. What I'd like to do is use the Substitute command (or possibly a script) to remove the first five characters in each question so the question number, the following decimal, and the leading space are deleted, leaving the field to begin with the first word of the instruction. …

Having an issue with different length months. I have a layout with a month date field and 31 vertical day fields. I need each record to start with the 1st day of the month, but only show the days of the actual month selected. Is it possible to not show the last field (Day 31) if I select a 30 day month and still remain in the same layout? Or some way to leave the Day 31 field empty and not filled with the 1st day of the next month. If I create a separate 30 day layout, it seems I'm unable to construct a found set containing all the related records for one person. So I presume I need to add some calculation to the day fields to avoid entering the next month days. …