Calculation Engine (Define Fields)

Firstly, sorry for the poor explanation but I am at a loss how to explain it in a few words! I am building a transport database which will provide a delivery schedule. In order to do this the record (in table named tblSchedule) needs to show three locations, origin, intermediate point and destination. Each location has a unique record number and address. All the location data is kept in the table named tblLocation. I have created a relationship between the two tables linked bt the location number. In tblSchedule I have created three drop down lists which reference the location number (Loc1, Loc2 & Loc3) from tblLocation and three address fields (Add1, Add2 &…

I would like to define a field that will deliver a summary of all sales in a calendar month, those sales in the month defined by the Invoice Date field for the current record. This way I will see at a glance how my monthly sales are performing. Can anyone help? Thanks

This question must be so dumb that nobody has asked it before because I can't find anything about it. I put the symbol for User Name on a layout. I expect it to be the User Name in security meaning their account name. I know it isn't working like I want because I created privilege for someone and logged in as them but the symbols on the layout still show my name. How can I fix this please?

I am trying to make a products picture database. To make things as easy as possible, the preferred procedure would be to simply right click the image in a browser, go to the right container field and paste. But the image size gets really bloated: As an example, I copy a jpg-image that the browser says should be 86 kb. But when I paste it into the container field, ithe actual size shows as 209 kb (Length(container_field)/1024). If I then export the field content, the exported image file shows as 196 kb. If I, on the other hand, import the jpg-image to my computer, and then inserts it into the container field, the size shows as 86 kb in Filemaker. I wa…

Hi, I am trying to find a simple solution to solve the following problem: I have a table that stores images for a product. Thus one product can have many images. To decide how they will be shown, these images need to be sorted, and have a unique sort value. Is there a simple way to find the highest number in the sort field where the product_ID is the same, and auto increment this? Also, does anyone have a good solution for changing these values in a simple way? Kind of the way tab order is set up in filemaker, so that if you change the value to a lower one, all the other images with the same product_ID will automatically increment their values with one.…

Hi, I have a problem for update my stock price. I have a table : product with productID,ProductName,qty,Price and Invoice table: InvoiceID,Invoicedate,Customername and InvoiceLine:InvoiceID,ProductName,qty,price,cTotal(qty*price). my problem is: If my porodcut price's(prodcut A=1$) change from (1$ to 2$), I want to see this change in my invoice. sample: 2012/4/12 1 Product A 1$ 10 10$ after change price: 2012/4/12 1 Prodcut A 2$ 10 20$ what I should do? thank you

I’m trying to extract the filename from a file path. For example: Path = C:\Maintenance\System\Temperature Guide.docx. It seems the position function does not operate on “backslashes”. When I try to find the first backslash from the right, I get the error message that the text constant does not end with a quotation mark. Position(Path,“\”,-1,1)

I have a report summarizing data on neighborhoods and on zip codes. There are two totals: revenue and people (Both summary fields) The two totals are fine in the summary reports, but I cannot get an average on the two totals What I would like is total rev/total people = Avg. Price I would like this to appear on the report by neighborhood or zip. I can get the total rev/total people to appear on each line with the same value on each, the aggregate total, but not breaking on each sorted field (neighborhood or zip). So far I have tried getSummaries on both totals and also running totals that restart on zip or neighborhood depending on which report I…

I have a StartDate (date) and a Frequency (text) field. I want to calculate the dates of the next upcoming frequencies based upon perspective of today. Weeks, Months, Quarter, Biannual Triannual and Yearly. I know how to get these results but not based upon today. Seems I have to first compare where I am relative to the StartDate. The current StartDates are several years old and need to stay. Weekly: start date is 1/7/2000 so Friday. Today is Friday (and can't count) so it should produce 4/27/2012. Monthly: start date is 1/1/2000 and today is 4/20/2012 so it should produce 5/1/2012. Triannual: start date is 2/15/2000 the next would be 6/15/2012 (sorry…

Hi, I'm very new to FileMaker having been tied to excel for many years In excel I have lists of Weights of Cattle going back many years which can give many fields ... 11 x 12 in the current example For Filemaker I have accomodated the data,(rightly or wrongly), in yearly records My first question is .... How to make a calculation to find the last entry , ie., "451" Thanks in advance, P.

uses.zip

I'm having a difficult time figuring out how to do something which I think is simple. I have a date_of_birth field. I have another field I need to populate with the person's 21st birthday date. I just need to add 21 to the person's birth year, but I can't figure out how to do it. I'd appreciate your help. Thanks, Kevin *** Thanks, but I figured out my problem. I stupidly forgot to change the calculation result to date instead of number. Works fine now with this calculation: Date (Month (Child_1 DOB) ; Day (Child_1 DOB) ; Year (Child_1 DOB) +21)

Hi there, I am trying to clean up a database of journal articles. I am particularly interested in identifying potential duplicates based on comparing the title of the article. I am thinking that a calculation could compare two text strings and return a "%similar" value, so that the following two strings would be flagged as a potential duplicate. "Deep-inspiration breath-hold PET/CT of the thorax" "Deep-inspiration breath-hold PET-CT of the thorax" As would these: "∆p in the thorax precipitates asynchromatic sarcoma" "delta-p in the thorax precipitates asynchromatic sarcoma" however this would get a low % similar rating: "Alpha beta …

I have an 'Image' field that I want to contain the same data as I type into the 'Category' field with some text appended to it. So, if 'Image' = "Jazz", I want the 'Category' field to add "http://domain.com/" to the front and ".jpg" to the end, so that it becomes http://domain.com/jazz.jpg I've been trying to do this with the 'Specify Calculation' option, where I entered: "http://www.domain.com/" + CATEGORY + ".jpg" but all I get is a '?' in that field.

"GOAL" :: to use the specific ids of the a single record to auto enter all the other 3 ids when one id is entered into respective table "accomplishing":: if i enter op_id in the table op_id from record 1of table ids it must have tables that is sym_id, rx_id,fu_id must have the respective ids that is sym_id,rx_id,op_id of record 1 of table called ids hope m clear if not i will try to explain again in better way i guess 4codes.zip

I have a portal from a table REFERRALS_2_SERVICES on a layout. With the following fields 4: ID_R2S.pk (number, Indexed, auto-enter serial Quarter (text) Sample data: Q1 Year (Number, in the Range 2012 to 2022, Max 4 characters) Sample data: 2012 Quarter_Year (text, Indexed, auto entry calculation replacing existing value) Sample data: Q1 2012 Service (Text) I may not have formatted the fields Quarter and Year correctly but it's the only way I could think of. I like to maintain several running counts calculations. Result A: I want to keep a running count by Quarter-Year (the number of times a new record is added that matches a …

Hi, I'm trying to figure out how to calculate the Leave Balance every Year, so that it accumulates every year according to the remaining or exceeding Balance from this year. Right now I have 4 fields: Field1 (Leave Entitlement) = Contains the number of Leave Days entitled to the employee at the beginning of each year. This number is fixed according to the the Salary Level of the employee. Field2 (Enter number of Days) = This is an Edit Box where the user enters the amount of Leave days the employee requested this year. Field3 (Leave Days Taken) = This is a Calculation Field showing how many Leave Days the employee have taken this year. (resets to 0 at new ye…

I'm currently working on a complex validation of data-types. Since the input is text (coming thru JSON over the net) I have to make formal checks first, all of the following are considered valid dates: "14/11/2004" "11/14/2004" "2004-11-14" I thought I could rely on the FMP engine and let it check if this day actually existed and was rather shocked when I debugged my findings: IsValid ( Date ( 11 ; 14 ; 2004 )) => 1 IsValid ( Date ( 2 ; 30 ; 2003 )) => 1 (there is no 30th of February) IsValid ( Date ( 99 ; 99 ; 1999 )) => 1 (Maya calendar?-) The last entry is absurd. Date ( 99 ; 99 ; 1999 ) => 07/06/2007 When I try to input "…

I have 5 fields in a table. I want them to be Number fields. But I would like them to auto-enter a value from another table during creation. The table is unrelated. This way I can have a user change the values in the unrelated table instead of changing the default value in the Manage Database function. Is there a way to have it grab a certain value? Below is what I am trying to accomplish. Main Table One Record has all three fields FabricateCost InstallCost ProgramCost Lookup Table Type (has Fabricate,Install,Program) Cost (has a 22 for Fabricate, 40 for Install, 60 for Program) So one record has Type = Install, Cost = 40 So when a user creat…

I want to run a report which works out the sales success rate of quotes submitted to clients for a particular month. The report sorts the records by quote status (approved; pending). The sub summary when sorted by quote status shows the running total value of approved and then pending. I want to calculate the percentage of approved for that month so i can track the monthly sales conversion rate. I cant work out how to do this since its the same field but in different part that i basing the calcluation on. I'm guessing there is probably a very simple solution to this but I have no idea where to start. Please help!!

how else can this function be modified

Hi I have a value list of ages it is: All 0-4 5-10 10-14 14-19 20-30 30-40 40-50 50-60 60-70 70-80 80-90 90-100 100-110 I set it up as a check box option in the field entry My questing is : How to I use a calculation to set the field with several options selected? In my script I used this: Questions::age&"40-50"&"50-60" The script is called middle ages because I wanted to select all those groups at once. I can't seem to get this to happen. I would appreciate your help. Thanks

random function how is this calculated

can anyone explain whats happening here logical mathematics is too much for doc brain plz help me http://www.mediafire.com/?86q21qnez1ncb6a

hello, hope somebody could help... i have a db with insurance clients whom i would like to congratulate on their birthdays; my task is to write a script which would give me a list of birthdays in a given period of time, e.g. this week, next month, etc... script which i "wrote": Enter Find Mode Set Field (Clients :: month; Month(Get(CurrentDate))) Perform Find allows me to list persons in a current month only; apart from that, the list shows mixed dates, is not chronological with respect to days any attempts to modify the script with "day" were futile; so obviously i still got to learn a lot... thanks in advance, fmfox

I CREATED 2 TABLES WITH 4 FIELDS IN EACH TABLE-1-----F1,F2,F3,F4 TABLE-2-----2F1,2F2,2F3,2F4 I MADE RELATIONSHIP BETWEEN 2 TABLES AND WHEN I ENTER INTO TABLE-2 F1 CONTENT OF THE LAST RECORD SHOULD AUTOMATICALLY BE ENTERED INTO 2F1 HOW TO DO IT I TRIED ALOT BUT IN VAIN , PLZ SOME ONE HELP ME

I need to export text that shows date and time in the format : "yyyy-mm-dd hh:mm:ss" The best formula I have found so far for the calculation field is "Year ( DateStart ) & "-" & Month ( DateStart ) & "-" & Day ( DateStart )& " " & TimeStart" However this gives results like ""2012-4-23 10:00:00" which has the problem of the month only having one digit when I need it to have two. Any ideas for resolving this? I also have the problem that the exported text should have a UNIX LF end of carriage instead of a Macintosh end of line. Can you implement a UNIX LF end of carriage from within FMP?

I have a field (concentration) that is a calculation based on three other fields: 1. Total volume (i.e., 25 ml) 2. Volume of chemical added (i.e., 0.00035 g) 3. Purity of chemical added (i.e., 99.8%) The field calculates a ppm value based on these three numbers, no problems there. The issue I have is that I need to be able to calculate the number of significant figures in each of the three source fields, then display the result with the proper number of significant figures. [0.00135 g (3 SF) x .99 (2 SF) ]/25.0 ml (3 SF) = 53.46 ppm, but it has to appear as 53 ppm because the result has to equal the least number of significant figures in any the source…

Hi everybody, I can´t get a formula to solve my problem, I´ve 3 fields, "Income", "Expenses" and "Total" like in this spreadsheet https://docs.google.com/spreadsheet/...zFvcHNad0dSYXc But i can´t get Filemaker to do the same operation. In the first row "Income" has a value and the "Total" is "Income - Expenses", but in the second row "Total" need to subtract from the previous "Total" and then from "Expenses" Any help?

Hi to all, How can i calculate time period from given Start_time and End_time. Ex.- As I have Start_Time - 09:00 and End_Time = 12:00. I want to calculate 10:00 and 11:00 as Time Period, between these given time.

Hello Everyone; I have never had a reason to do this, but now i do. I have some repeating fields that store global storage for questions being asked. In my form , i have another repeating fields for the answers. NOw when user is finished answering questions i am confused as to how to print this via a list as the questions are quite long>! I want just the header to have user & question / answer date In the body i want Question [1 ] Answer [1] -------------------------------- Question [2 ] Answer [2] _______________________ etc But i don't know how to do that! Calculation repeating field, script that loops through the records and creat…

I want to create a field that will display a calculated text result based upon several checkbox fields. Basically, I have 4 check boxes that can be selected and unselected independent of one another. I want to display a friendly result that will display the name of all check boxes that are checked. i.e. field1 = 1 field2 = 0 field3 = 1 field4 = 0 would return "Field1, Field3" and field1 = 1 field2 = 1 field3 = 1 field4 = 1 would return "Field1, Field2, Field3, Field4" What's the best way to do this?

Can the following calculation be expanded on so that it only lists unique values? Substitute ( List ( Assessment Questions::Hazard No ) ; ¶ ; " " )

I was wondering if anyone has experience with google spreadsheets. I have a filemaker solution whose main purpose is to perform a lot of calculations. I feel like I have written my scripts, calculations, and custom functions to be as efficient as possible, but to calculate everything I'd like to, with one machine, would take 70-125 hours, every day (which means either having a whole lot of machines each doing part of the work (as it can be segregated), or doing only 10-20% as much as I would like to, and still having the cpu of one machine largely-consumed most of the time). I was wondering if google spreadsheets might be able to run these calculations much quic…

The below is an extract that I've seen on another forum. It's something that seems quite interesting with regard to the last portal row (ghost row). So a calculation can be written that will either allow an object to appear on a layout or not dependant on criteria? Is this easy to do? ' I make the delete button for every portal row to be a calculation, so that, if IsEmpty ( Portal row ID ) don’t show the delete button, so the ghost row at the bottom of your portal, don’t have a visual delete button, and of course the script that delete portal row just work if the portal row ID is not empty. This method that I’ve learn make the portal looks like a spreadsheet for the …

Good Afternoon, I'm attempting to create a database that records violations on driver logbooks. There is one log/record per day per driver. There are 11 violation categories, and 85+ violations. Right now, each violation is its own field with Y or N radio buttons (Y=1 and N=0). I need the simplest way to be able to create a report totaling the number of violations per catergory in the record, and number of violations total for the record.

Hi all.. Product Price Field (10 repeat) Currency Select Field: Usd / Eur / gbp TotalPriceField : (Sum=PriceField) and Report Layout Summary Fİeld = Total of TotalPriceField (so far everything works properly) Summary Total Field = Total Of USD Selected Record Total Price and Summary Total Field = Total Of EUR Selected Record Total Price and Summary Total Field = Total Of GBP Selected Record Total Price Sample View : (Report Layout) Order 1 Total Price 50 USD Order 2 Total Price 50 EUR Order 3 Total Price 50 GBP Order 4 Total Price 20 USD Order 5 Total Price 10 EUR Order 6 Total Price 30 GBP -----------------…

Hi all.. Pricefield (3 repetetions) Total (Sum=Pricefield ) (Radio button) (Value List) Currency Selection (USD - EUR - GBP - TL) i want the, change in the total price in the currency of the selected format sample : select USD, Total Sum = 75 $ select GBP, Total Sum = 75 £ select EUR, Total Sum = 75 € Thanks..

First of all I apologise because I think I’ve seen something similar in the forum but I can’t find it. I have two tables Subjects Exam questions In subjects table I set the number of lessons, i.e. 8. In the exam questions table I have a lesson field were to put its number, say 1, 5, 6 and 8. I have a graphic that shows how many times a lesson have been asked, that works ok. What I’d like to accomplish is to also know what lessons haven’t been asked. In the given example would be 2 to 4 and 7. Thanks to all.

Hi. I have created a FM database on a friend's Mac to record invoices that I receive from one employment agency. I have fields for invoice number, staff name, no. of hours and hourly rate, milage, and the cost which is a calculation field. Sometimes, I have up to five records with the same invoice number and date, but different work dates, hours worked etc as several days may be on one invoice. I want to create a field that adds the end total cost of each field that shares the same invoice number but am struggling. I would also be interested to know if it is possible to make FM stop asking for a "date paid" if an invoice has not been paid. I tried …

I have looked @ Agnés Barouh's chargeFoundSet and fell in love with what she did. She is a great developer and was trying to do some tests out by making my own calc call a calc and it works. I was wondering if this was desired behavior or is this something i should shy away from? thanks -i *ps - this is the link with her amazing calc.. http://fmforums.com/forum/topic/61394-found-set/page__p__290371__hl__chargefoundset__fromsearch__1#entry290371

Hello, Is it possible to have a numerical field that is referenced within a text calculation display as percent? Right now it will only display as decimal: My calculation is: If(PO Tax flag="Yes";"Sales Tax @" & Tax::Tax Rate;Resale Information). Currently the Tax::Tax Rate information displays as "Sales Tax @ 0.0775" and I would like it to display "Sales Tax @ 7.75%". I have to keep my calculation as text so that "Sales Tax" displays and I've tried formatting the Tax::Tax Rate field to display as a percent but of course it only does works in the table it resides in. I feel like there's got to be a quick and simple work around to this but nothing is com…

Is there a way in the data calculation that would delete a record if it was more than 2 days old. If so could someone please write me up a sample. (Hopefully it will be a easy one.) Thank you in advance

Hello everyone; i was wondering as i don't see any built in functions to check for "A" vs "a" how i can do that! I even looked on bd's website and can't find matchCaseUpper or Lower,etc I was trying something like this, but no luck yet! Let ( [ word = "ArChiVe"; // my word marker = "^case^"; // this makes it unique find = marker & "ArChiVe" ; // ^case^ArChiVE result = substitute ( word; "ArChiVe"; find ) //What up? ]; position( word; find ; 1 ; 1) ) - i don't think i am close, need some advice please.

hello everyone; i just can't seem to figure this one out. I am looking to find a certain range of chars such as A1, then strip chars before and after but don't know how. here is what i have so far. I just need it finished. * ok. while i was writing this, i saw that i figured out my own problem Here is the solution for anyone else; Let([ text = "1ASdfdA1sdjf1231123"; //string to search for ; search = "A1"; // i am looking for A1 location = Position ( text; search ; 1; 1); // where does this start @ sectionR = Right ( text; location); // find right section sectionL = Left( text; location) …

Based on a bug discovered by Federico Severin bug\'s joke.zip

I need to import a file that has a field in which the individual words in a name have been combined into one word (i.e. SmithRooftopBuilding). I need a calculation that would separate the words at each capital letter so that it would read "Smith Rooftop Building". I assume the easiest way to do this would be to write a calculation that inserts a space before each 2nd capital letter in a text string. However, I'm having a hard time doing it. Any help would be appreciated.

Hi guys, I'm designing a database for a small company and I was thinking about implementing a calendar which could sum up all the things that have to be done daily. As an example I have a Payment table linked on a 1to1 relationship to a date table, i.e. paymentID = paymentID(in dates) AND dateID = dateID(in payments). Each payment has a date (and some calculation is going to be run on that date). I'd like to display those dates in some sort of calendar so that the user could see each day what payments (or other stuff) he has to do. I found some solutions online for filemaker calendars and I wanted your advice about that. Shall I create a calendar from scratch…

Let ([ $n = $n + 150 ; Calc = "getnthrecord (TableA::ChargeFoundSet; $n ) & ¶ & " ]; Evaluate ( Calc ) ) thanks, -i

Could a text calculation be written to remove the LN3615 and the following single character space from the below field to leave 'Helpston Jn to Syston South Jn'. It would be ideal if the calculation could remove all characters up to and including the first space as sometimes the code could be five or six characters. LN3615 Helpston Jn to Syston South Jn BTW I am aware that is it the fact that I have two items in one field that is making this calculation necessary. It's an inherited file and I thought it would be interesting to see the calculation.

Hi guys, I have a pretty basic database that I need to add a summary report/layout to. The database tracks the number of light bulbs out in a warehouse, and will be used on an iPad. There are 10 "wings" of warehouses, A-I, with each wing having a certain number of warehouses within it. I have one table in the database, tbl_data_tracking, with the fields: Date Warehouse Metal Halide (type of bulb) High Pressure Sodium (type of bulb) I have 10 layouts (one for each wing) and 10 value lists of warehouses. For example the A Wing has 9 warehouses, so the "A Wing" value list is (A1, A2......A9.) On each layout is the Date field, Warehouse field (…

Hi I've been given some data that I need to modify. Basically its a list of clothing in the following simplified record format: TS001, T-shirt, Black, Small, $5.00 TS001, T-shirt, Black, Medium, $5.00 TS001, T-shirt, Black, Large, $5.00 TS001, T-shirt, Black, XL, $5.00 TS001, T-shirt, Red, Small, $5.00 TS001, T-shirt, Red, Medium, $5.00 TS001, T-shirt, Red, Large, $5.00 TS001, T-shirt, Red, XL, $5.00 TS001, T-shirt, Blue, Small, $5.00 TS001, T-shirt, Blue, Medium, $5.00 TS001, T-shirt, Blue, Large, $5.00 TS001, T-shirt, Blue, XL, $5.00 TS002, Hoodie, Black, Small, $9.00 TS002, Hoodie, Black, Medium, $9.00 TS002, Hoodie, Black, Large, $9.00…

Hi. I am trying to create an IF statement to determine if the Date Booked for an event is greater than 1/31/2012 AND if the record is tagged a certain company. Right now I have... If [services::Date Booked > 1/31/2012 and Event::INC_LLC = "LLC"] Problem is, if it is an LLC event, it always validates true regardless of what the date is. What am I missing? Thanks, Rich

Hello, I have a database in which i have entered more than 100 records. After a while I realized that i need an extra field. Is there any way to enter the same entry (for example 1) in all 100 records? I don't want to make it Global field because the value of the field will be 1 or -1. So the Global field option doesn't solve my problem. Thank in advance.

hello, I have a summary field which is: Sum ( T12i_invoices_LINE_ITEM||flag_product_id|::Quantity) I want the result of it to be an absolute number. How is the way? Thanks in advance.

I have the following summary field which gives mileage in one direction. I need a field that gives round trip mileage which basically is sCountReportMileage (Count = cReportMileage) * 2. This gives the correct value in the source table but only gives a value of 2 when I place this field in a related table. sCountReportMileage (Count = cReportMileage) displays the correct value in a related table. I think I need (open to suggestions obviously) sCountReportMileage (Count = cReportMileage) * 2 to be a summary field in it's own right and not a calculation field. Any ideas please?

Is there any way to verify an email address during data entry? Same question applies to a web address. Right now to verify a web address we have a script that uses open url and fires up the web browser. For email verification the script copies the email address and then opens up a web site that the user can then paste the email address into a box (on that website) to test if the email address is valid. Both of these approaches are kind of clunky as I would like to keep the users in the FM environment and not distract them by opening a page in a web browser. I didn't know if there was a way to test or verify the web/email address and then repo…

I am compiling a library of books which have a "series" number, and a "volume" number. So for instance: Book Title, Series, Vol in series My book, 1, 3 His book, 2, 4 Her book, 3, 7 I want to sort this by a book's position within the whole series, so I need a calculation field to do this for me, which I can then ask FM to sort. What is the calculation I need to do this?

Hi! I´ve been trying to get the field name of a portal field, but the expression Get(ActiveFieldName) refers to the name of the field of main table in the layout. Any help would be highly appreciated. Thanks.

Hi, I have a problem with my database.I want change my price's of product, and I in portal happen this. I mean, If Product A price = 100 after insert in lineItem, if I change this price (for sample 200),then in portal I see this new price. Please see my database and help me,thank you. Stuff.zip

I have a calculation which contains carriage returns. I use a script which uses the export fields as a text file. When I open the file in Windows it does not recognize the carriage returns. Is there a way to modify the calculation to account for the way Mac and Windows handle the carriage returns? (Using FM 8 and 9)

Hello, I have a field that contains a calculated result. Actually it is the sum of the line items. Sometimes the invoice of a corporation has the sum of 1167,06 and when i enter the products to an invoice of my database i get the total as 1167,07. In order to control my payments it would help me to have the same result exactly. So, it would be helpful to have a calculated field that at the same time to be editable, so when i have problems as i described above to have the option to enter the sum manually. thank you in advance.

Hi all, I've been going nuts trying to figure out a formula, so I'm throwing in the proverbial towel and requesting help from one of you calculation gurus. Here's what I'm hoping to accomplish: A calculation that determines how many pieces of material of a given known size (W x L) can be cut out of sheet of material that's a larger size. I've written a calculation that gives me the result for the number of pieces in a tidy little x-y configuration. FYI It goes as follows: Case ( //test to see which orientation of axes yields the most pieces Floor( materialSizeW / pieceSizeW ) * Floor( materialSizeL / pieceSizeL ) â�¥ Floor( materialSizeW / …

I have a global field that is being populated with some information from an html file and I need to clean that field data to create a value list. Global field data looks like this: <option selected="selected" value="<ALL>">&lt;ALL&gt;</option> <option value="Domain\aaRequestor">Requestor, </option> <option value="Domain\UserID1">lname1, fname1</option> <option value="Domain\UserID2">lname2, fname2</option> <option value="Domain\UserID3">lname3, fname3</option> <option value="Domain\UserID4">lname4, fname4</option> <option value…

Just wondered what the key differences are between auto entering a lookup or a calculation as they appear to do the same thing. I have got Filemaker the missing manual 10 which has an explanation but they both still seem to do the same thing to me. Any enlightenment on when to use one or the other would be greatly appreciated.

Hi, I have two tables with related "department" fields: Awards (table) Department (text field) Person (text field) Grant_Contract (text field) Department (table) Department (text field) Contract_Person (text field) Grant_Person (text field) If the Departments are the same in both tables I'd like to pull in the appropriate person from the Department table based on the data contained in the Awards::Grant_Contract field. Example: If the Awards::Grant_Contract field = "Grant" then the Awards::Person field = Department::Grant_Person field. I've tried this caluculation for the Person field in the Awards table: Case ( grant_contract =…

Hi, Im not sure what this is a called, so I do hope that I can explain it properly, and that Im posting in the proper place... I enter data into a field. That field is then used by another global field which calls its data through a calculated result an displays it for printing. The issue is that once the data is displayed for printing, "?,?,?" is added (without the quotes) to the end of the text which was originally entered. The following is the calcutaion used for the Calculated Value...I did not include the custom function as it is used (i think) by others and this is the only place I am aware of this issue. Any tips as to what is happening would be gr…

EDIT: Please refer to post #5 Hi guys, I have to deal with this situation and I'd appreciate your advice: table: A (parent_key: aID) field: Address in table B (foreign_key: bID = aID) I want to put 2 different addresses from records of table A. I'm wondering if I have to put a second parent_key in table A and create a new relationship, i.e. table A (parent_key1: aID ; parent_key2: a2ID) table B (foreign_key: bID = aID, foreign_key b2ID = a2ID) to have in table B: address1 from table A address2 from table A The point is that I need table B to have 2 fields, 1)From: address1 and 2)To: address2 and both address 1 and…

Hi, For a specific function, I need to fetch a specific field from a related table. An assignment can have multiple external file numbers. assignments (_pk_assignment_id, ...) externalFileNumbers (_pk_fileNumber_id, _fk_assignment_id, type, fileNumber, ...) I need to get the value of the fileNumber where the type = "NAS". The SQL equivalent would be. SELECT externalFileNumbers.fileNumber FROM externalFileNumbers INNER JOIN assignments ON externalFileNumbers._fk_assignment_id = assignments._pk_assignment_id WHERE externalFileNumbers.type = "NAS"; This is not for display, so no portal filtering... Thanks for any help

Hello, Could you please help me with an issue that is while the sum of the line items of an invoice is 190,43 the invoice itself shows the total as 190.40. What could be the solution? I tried to upload the file but i couldn't. It is the FM starting point file. Thanks in advance.

Hi Everyone, I am trying get the ABCD to work. Is there any way to categorize the "0" to read any letter instead of a number? Part number ex: is SR18.1111 (yes this is a real image name). Case ( IMAGE_NAME ≥ 6000 ; 6000; IMAGE_NAME ≥ 5000 ; 5000; IMAGE_NAME ≥ 4000 ; 4000; IMAGE_NAME ≥ 3000 ; 3000; IMAGE_NAME ≥ 2000 ; 2000; IMAGE_NAME ≥ 1000 ; 1000; IMAGE_NAME ≤ 999 ; "0000"; IMAGE_NAME ≤ 0 ; "ABCD"; ) EX: if it's a part number under 999 then the directory should be 0000 but if it's over 1000 but under 2000 then it should be 1000. etc 2filemaker.zip

Hi all. Can someone help with this issue. I have a field called "home address" it includes the house number as well as the street name. It looks like this: "123 King Athur St" I need to have the number in its own seperate field. I have created a field "Street No" which should then contain the number "123" from the above example, and the field "home address" should then only contain "King Athur St" Thanks in advance.

Hi, Please look to my database and help me for resolve my problem. I want to do with my database: 1-after purchase a product,if I received some of it(Qty original= 5,I received 3), in purchase line item insert 3 and balance show 2. 2- How I can design a product History(Qty of received, Qty of sold,... in a layout) Thank you. Stuff.zip

Hi All. Once again, a bit of advice will be very much appreciated. I have phone numbers in my database FM Pro 11 that are not entred in the correct format. the numbers should be as follows: (555) 123-4567 This is the exact format I require, including space and punctuation. So, I have numebers like: 5551234567 or (555)1234567 and 555-123-4567 etc. I remember reading somwhere how to fix this so that they all read (555) 123-4567 But I just cant find the thread. THEN, I need to be able to also export the telephone numbers to Excel, but the telephone number then has to read 5551234567 with no spaces or punctuation. Secondly, I have all my s…

I managed somehow to program myself a Book Inventory database that has a number of fields, such as for instance: Now, I would like to know a single calculated (?) field which taking from the aforementioned fields would give me this result: Specifically, the calculation should: - take the 1st letter from the 'Author Name' field, trim the rest and add a full stop (John -> J.) - take the value from 'Title' field and put it in italic - take the rest of the values from the other fields - Finally display in one tidy line and in a single field the whole bibliography quote ready for me to copy it when I need to I wonder if that is possibl…

Some of my members are snowbirds who go to Florida or Arizona for the winter. Some have a summer home in the mountains where they go in the summer months. I have fields which contain this alternate address, as well as the start mm/dd and end mm/dd text fields for their stay at the alternate address. These rarely change from year to year. There are startDate and EndDate date fields as well. An AtAltAddrFlag simply calculates if the current date is greater than the start date, and less than the end date. The problem is how to calculate the correct year for the start and end date fields. If Joe Snowbird goes to FL on November 15, I would use 11/15/11 for the sta…

I am almost done with my books database even thanks to Bcoon that helped me out with a (for me) complicated calculation field, nonetheless I encountered a few problems that I have no clue how to solve, I know I am asking a lot but I truly can't manage with my limited FMP knowledge at the moment and I was wondering if anyone can lend me a helping hand. I attached the DB so far below, basically what I can't manage is: 1) On the layout "List" I created a number field where supposedly should go an increasing auto-entered serial number (not record ID) that, when I display the list and sort it by author, year or subject should end up displaying results accompanied by an in…

i would like to write a calculation script to deny access to 'Delete Record�' and 'Delete All Records�' menu items for guest users. All know is that a non-zero value is true and a zero or empty value makes it false (access denied). Otherwise, I have no idea how to write this. Can anyone tell me, in some detail, what I would enter in the calculation script field to do this ? The window where I need to enter code:

Please follow with me. I've been trying to get this to work and searching everywhere. Seems no matter what i do i get a different result. Field1 - This field contains a bunch of data i need to separate. The field will contain one continuous line of numbers separated by only "-". Normally this will be sets of 3 numbers only, but for some reason the data being received will have sections where a 4th "-", is added but this needs to be combined with the 3rd. Example with 3 numbers: 2.345-1.2345-4.567 Calc1 = 2.345 Calc2 = 1.2345 Calc3= 4.567 Example with 4 numbers: 2.345-1.2345-4.567.-123 Calc1 = 2.345 Calc2 = 1.2345 Calc3 = 4.567.-123 I…

Hi guys, I'm building a table for Purchasing Orders, and I have a problem. I have a field in "Purchasing Orders" table named "Supplier name" set up as a drop down menu linked to a value list obtained from the table "Suppliers". In the "Suppliers" table I have "street" "city" and "state" fields. In "Purchasing Orders" I also have 3 fields named "Street", "City" and "State". I would like Purchasing Orders' fields (street, city, state) to be auto-completed when I choose the wanted "Supplier name" from the drop down menu. Do I need a script (or calculated fields) to achieve this or is there a way I can make it without it? (The 2 tables are obviously r…

My company uses filemaker to help keep track of parts, quotes, processes, etc... Many times we have to enter data two or three times for the same thing. For instance. I will make a quote with Part name, Part number and price. then I will have to go in an enter that same part name, part number and price into our part index. What can I do to port all the information to that part index. I'm assuming a script button to trigger some of the selected items. but i'm lost has how to start.

I would like to have a pull down or even a selct list to click on to have the system go out and find previous year. I already have a script for current year. thanks in advance.

Task: Recieve input from user (using keyboard or barcode scanner) Lookup in Table_1 to see if the value exsists in the field "EAN" in any record If NO: show my error message (Other than numeric characters found or too few/many digits). If YES change the value of the field "Status" in the found record from "Inactive" to "Active" I need to validate the input to ensure that the format is excatly 13 digits (EAN-13 barcode). The barcodes in Table_1 is stored in text format. P.S. It would be nice to ask the user if the scanner returns only 12 digits: "Error! 1. Edit by keyborrd? 2. Add leading zero automaticly? 3. Cancel? P.P.S. Please excause my clumsy engl…

I have a date field in a customer order entry layout. I have put a custom dialog in the field properties if validation fails. It is done by calculation which I guess explain why it is the only field that shows a ''Revert'' option in my custom dialog box. As users don't have a clue what this is, I need to get rid of it. My calculation to validate the date is as follows: DateCedule ≥ Get(CurrentDate) and DateCedule ≤ (Get(CurrentDate)+184 That translate to anywhere between today's date and no more than 6 months from today. I read in forums that the common way to avoid the Revert in a dialog box is to include a formula that changes colo…

First post here: after 3 days of reading internet forums and watching videos I surrender! I'm working on a db where i have a value thats sell price for an item. what I would like to happen is to be able to input the price in sell price and then have sell price + sales tax + credit card processing fee and cash sell + sales tax both appear based off sell price + freight.. the closest I've come was being able to add freight and sale price to display.. any help is greatly appreciated.. thanks dazed, confused. here is the attached DB zip file. invendb.zip

Hi, I have a problem with report, I have three table(product,invoice,invoicelineitem) and want to know for each item how many I sold(qty) for example: Invoice: Invoice:1 Item A qty::5 Item B qty::6 Invoice::2 Item A qty::7 Item B qty::6 I want see in report: Item A qty::12 Item B qty::12 Stuff.zip

Hi, Please tell me about global field with a sample. Thank you.

I'm populating a value list from a field containing all the file names in a directory (hundreds of values). I need to filter it to only show files with a .txt or .TXT extension. I can do it using a loop in a script, but I'm hoping someone can point out a simple calculation technique. BTW, I've looked at a couple of functions on BrianDunning.com and found them too involved for this simple case. Others may eventually look at my code! Plus, I'd generally like to know how to filter fields for other reasons. Thanks for any help.

Ok, here is my problem, I'm hoping it's not impossible: I have a bunch of TXT files, which have HTML code in them. Each file has the same layout and stucture, but with the Customer Name & Address and Product Name & Desciption being different on each page. I want to strip all the other info from all the text, and end up with only 2 fields, Customer Name & Address and Product Name & Description left. Is that possible? Thanks guys, really appreciate the help!

Hi folks I'm having a problem using the MAX calculation. I have one table linked to another via URN and I just want to select the max value in the linked table by the max (or latest) date. So, if on 18/2/11 I have a record with an N (in the approved field) but on the 23/2/11 I then have a Y in the approved field, how do I automatically select the Y? I'm sure this should be simple and I know I've done it before...but it's just eluding me this morning and doing my head in!! Is this the right forum to be asking for help? Thanks all, Simon

Hi, I want to know qty in hand after sold qty according with my database. I have this tables: Board:Board_ID,Board_Name,FirstQty,Price,cStockLevel Invoice:INV_ID,INV_Date,cTotalInvoice InvoiceLineItem:INL_ID,INV_ID,Board_Name,Qty,Price,cLineTotal cLineTotal=sum(FirstQty*Price) cTotalInvoice=sum(InvoiceLineItem::cLineTotal) cStockLevel=FirstQty-Sum(InvoiceLineItem::Qty) I need to know after sold, qty in hand =? thank you.

Hello, for days I am trying to figure out how to do this- maybe it is easier then I think: on a multiuser enviroment all on fm pro 11, I would like to use a mix of the virtual list technique (bruce robertson?) with displaying related data for input through a portal. Anyhow what I would like to do is this: create a multiline key to display data that is filtered dynamically. the approach: parent connected to child via x cartesian list (fieldfromchildindexed) the field I am listing displays two fields in one line like so: categorykey||keychild The list will retrieve no more then 6k records max. Now what I would like to do is remo…

Hi guys, I'm a new FM user and just signed in the forum, hope to be posting in the right section (please move the topic if not). This is the problem I got: I have 3 fields named euro - usd - chf , set as currency values. Whenever I enter a value in any of those fields a script triggers and it calculates the currency value, i.e. if I type a value into "euro" I get the equivalent in usd and chf. I have a "Manufacturing" table which contains theese currency fields. For example: ManufacturingID = M001 Cost: Euro = 32 Usd = 42 Chf = 38 Sometimes it could happen that I need to change this manufacturing cost and I want the old cost to be save…

Does anyone know of a pre-made pro-rata calculator for filemaker? I need to integrate one with our database. In insurance we use pro-rata calculations daily. Thanks Matt

Can someone please explain why my two repetition calcs in the attached file are behaving differently. I don't see why a hard-coded value does work, but referring to a global does not. Referring to globals seems to only work for repetition 1. Thanks, Barbara PS: Spend more time answering than asking, Lee, lol. repCalcTest.zip

I am trying to create an autogenerated Id number for an invoice but this number must be prefixed by the year for example 2012/1 however at the start of each new year these number need to go back to 1 so for example the first invoice next year will be numbered 2013/1. can anyone suggest the best way for this to be done. John

Greetings, I am new to FMPro and could use some friendly advice as my current task has me stumped. Background: I am using three independent database files (.fp7) Contractor Contact Management Project Database Contractor Work Log The project database and contractor contact management files are for internal management personnel use only, while the contractor work log will be accessible to multiple outside vendors. Contractor work log: Using relationships, I have been able to create dropdown lists to populate field data such as contractor name and company (from contacts data file) within the work log. Additionally, I created a portal to log mul…

Just started using FileMaker. Have a situation where my student DB calculates the total hours based on a 42 week year. Student signs up, i insert an 'startdate'. When students terminate, I punch in a 'EndDate' which is kind of ok with regards to the actual dates. Problem appears in the calculation field of the 'StartDate' to = 'LessonTotal' because this calculation method continues even after the student has resigned. I need a script to stop this from happening, say if I select 'Inactive' status or if I enter an 'EndDate'. Any help would be much appreciated, Rena

I created a database off of the Lending Library template for a lending library I own. I am a VERY basic user and have been stumbling trying to figure things out as I go. However, I desperately need to figure out the reservations portion as I'm afraid I'm going to have double bookings on some of my items. I basically need an automated way to let me know how many of that item is available for the date range I am processing. Right now, I enter each reservation (see reservation screen shot) THEN I have to go to the inventory entry (see screen shot) and manually check to see if the item has a conflict. I'm basically doing double the work which is VERY timely (especial…

I am trying to mix true dates into a text situation and get date back. I have fields in Checklist for: Days (they can enter any whole number for number of days) WhenDue ( text is Before or After as an option) Dates (text with options of "Acceptance", "Check-In"; "Check-Out" or "Created") <--- this tells them which date to base their 'formula' after This stores the rules about automatically dating new Task records. Then in the table Tasks which is related 1 to 1 to Checklist, I have auto-enter by Replacement calculation and I want a date returned. It works as a text result, giving me 3/3/2012+1 or 2/25/2012-3 but when I change the calculation to D…