hal9000

I guess it wasn't, because when I created a calculated field based on the number field and used the getasnumber function, it sorted properly. I guess I thought the act of importing numbers into a number field would make them numbers, but I guess that's now how it works.

I have a number field with values ranging from 1-50. When I sort on this field, it sorts like text, like this: 1 11 12 13 14 15 16 17 18 19 2 20 There must be a way to sort like a number, no? Thanks!

They are separate text fields. It's as if the relationship between Customers and Orders only looks at the first related order it finds. Calc for Widget A status field in Customers: If (Orders::Type="Widget A";Orders::Status) Calc for Widget B status field in Customers: If (Orders::Type="Widget B";Orders::Status) I have a customer who has ordered both Widget types, yet their Widget B status field is blank.

I have two tables: Customers and Orders. The products that can be on orders are: Widget A and widget B. Each kind of widget can only be ordered once per customer. The order status can be backordered or shipped I need a report or export where each customer appears only once, in a single row, and with a column for each of the Widgets ordered. Like this: Customer, Widget A Status, Widget B Status Joe Customer, backordered,shipped I have tried everything I can think of: o A calculated field for the status of each widget in Orders o A calculated field for the status of each widget in Customers o A report or export with grouping Every time, for customers that have ordered both widget types, the status for only one of the widget types shows up in the report or export.

I have a layout that contains 33,466 records. But when I do a tab-delimited export and open in Excel, I get 33,491 records. Not only are there more records than in the source of the export, but some of the records that are in the source are missing from the export! (But represented by blank rows.) I traced it down to records that don't have a corresponding record in a related table. (But then I removed that relationship and it did not make any difference.) Call me a purist, but if I'm looking at a found set of x records and I do an export, I expect to see x records in the export, with no additions or subtractions! In this case, it looks like there are additions AND subtractions. Any solutions?

Great -- that works like a charm! Thanks!

I have a calculated field that makes up part of the body of an email. It looks like this: "Total new charges:" & $InvoiceTotal When this prints, if the invoicetotal is 79.90, it prints 79.9. If the invoice total is 79.00, it prints 79. How can I get it to always print with 2 decimal places?

Awesome, that worked. I guess I was missing the serial# field to do a proper count. Many thanks, you saved my bacon.

And when I move that calculation up to the preceding summary level, I'm back where I started, with an inflated total.

Thanks, that helps me to understand what's going on, and that may ultimately lead me to the solution, but for now, I'm still stuck. The reason is, I can't just sum the count of detail records because that gives me an average. For example: 11 entries sum to 40.0 hours (11 * 40.0 = 440.0) 8 entries sum to 34.1 hours ( 8 * 34.1 = 272.8) The total I need to get to is 74.1 (40 + 34.1) The total I get by dividing 712.8 by 19 is 37.5

I'm trying to define a summary field that summarizes a calculated field that as part of its calculation includes getsummary on another field. I defined the field, but the result are whack! I have attached the fp7 in a zip and a pdf showing the whacky total regular hours calculation. (712.8 and 600.0) Any help would be appreciated. The accounting department is breathing down my neck! Thanks! TimeKeeper.fp7.zip TimeKeeper.pdf

I have a customers table and an orders table. I want to create an occurrence of the customers table that only includes customers with no orders in the orders table. I have a customerID field in both tables. I added a relationship between the customers2 table occurrence and the orders2 table occurrence. The relationship is a "not equal to" (equal sign with a "no" line though it) relationship. So I expect a layout based on the customers2 table occurrence to include only customers with no orders, but no, it shows all customers. What am I doing wrong? Or what should I do to make this work? The purpose of the customers2 occurrence is to use in a value list. Thanks in advance, Hal

All of a sudden, the text in the export Field Order text box is very tiny. (FM 6 on Win XP - see print-screen attached). We installed a wide monitor 2 weeks ago, but the problem just started occurring today. Any ideas? (We're not quite ready to upgrade yet, but this might push us over the brink.) Thanks!

I have a report layout that does exactly what I want. See pictures. (The Total Sales field is a summary field.) BUT, when I export this report from Preview Mode, it looks nothing like the Preview. I've tried tinkering with sorting and grouping, to no avail. Exporting to Excel gets me a little bit closer. There must be a way to export precisely what is shown in the preview, no?

I have a single table solution to track feature requests. One of the fields is "Complete". I want to use a checkbox on a layout to allow the user to toggle the value of Complete between true and false or 0 and 1. How do you do this in Filemaker? Thanks!

Thanks, that worked.

I have a Summary field called MinDate that returns the Min of a date field called TheDate. Then I have a Calculation field called MinDateWords that returns the monthname and year of the MinDate summary field. I want to use the MinDateWords field in a Sub-Summary that breaks on OrderID, but it is giving me the minimum of the found set, and I want the min of only the order that the Sub-Summary breaks on. I have checked the sorting and I know it is correct because the MinDate field in the same Sub-Summary is properly returning the minimum date for each order, not the entire found set. To reiterate, the problem is that MinDateWords is returning the min value of the entire found set, not just the Sub-Summary. Is there any way to get this to work? Or can I use the monthname function on a Merge Field? (I tried, but could not get that to work either.) Thanks!

I have a table called Calendar containing a record for each period. The fields are: PeriodID Text EndingDate Date gCurrentPeriodID Text / Global I want to be able to access gCurrentPeriodID and corresponding EndingDate from anywhere in my solution. I think I need to create a relationship that allows me to look up EndingDate where PeriodID = gCurrentPeriodID. I tried that, but could not get it to work. What's the best way to access gCurrentPeriodID and corresponding EndingDate from anywhere in my solution?

Got it, thanks. I used this formula to do an exact find: "==" & $IndustryProduct

I'm trying to specify a Find Request for a Perform Find script step. The Action is Find Records and the criteria is: Charge::IndustryProduct:[==Get(ScriptParameter)] But this takes Get(ScriptParameter) literally. I also tried using a variable. How do I get this Find Request to use a ScriptParameter or variable? I also need an exact match, so I need to precede it with a ==.

Solved it. Where I had: Set Variable [ $OrderID; Value:"gOrderID" ] it was storing the literal gOrderID, when I really wanted to specify the global field there. Once I did that, it worked fine. (I did find a couple of other problems, but solved those as well.) I don't think I could have solved it without the feedback from this board telling me that I was on the right track. Many thanks to all. Hal

The script steps after the # comment step do not seem to execute. The OrderCharges for adding all belong to the same Order and that's why the OrderID doesn't change within the loop. The primary key for OrderCharges is OrderChargeID and it is generated automatically.

I have two tables - Charges and Order Charges. I do a find for all Charges matching the specified Industry and Product. This part is working perfectly, returning 3 records in the Charges found set for the data I'm testing with. What I can't figure out is how to add a new Order Charges record for each record in the Charges found set. Please see my script below. Thanks in advance for any suggestions on how I can add the new Order Charges records from the Charges found set. Commit Records/Requests [ No dialog ] Set Variable [ $OrderID; Value:"gOrderID" ] New Window [ ] Go to Layout [ “Masterfile - Charges” (Charge) ] Enter Find Mode [ ] Set Field [ Charge::IndustryProduct; Get (ScriptParameter) ] Perform Find [ ] # above works, below does not. Go to Record/Request/Page [ First ] Loop Set Variable [ $ChargeID; Value:Charge::ChargeID ] Set Variable [ $ChargeDescription; Value:OrderCharge::ChargeDescription ] Set Variable [ $Quantity; Value:OrderCharge::Quantity ] Set Variable [ $Rate; Value:OrderCharge::Rate ] Set Variable [ $UnitOfMeasure; Value:OrderCharge::UnitOfMeasure ] Set Variable [ $BillingFrequency; Value:OrderCharge::BillingFrequency ] Set Variable [ $BillingMode; Value:OrderCharge::BillingMode ] Go to Layout [ “OrderChargesTableView” (OrderCharge) ] New Record/Request Set Field [ OrderCharge::OrderID; $OrderID ] Set Field [ OrderCharge::ChargeID; $ChargeID ] Set Field [ OrderCharge::ChargeDescription; $ChargeDescription ] Set Field [ OrderCharge::Quantity; $Quantity ] Set Field [ OrderCharge::Rate; $Rate ] Set Field [ OrderCharge::UnitOfMeasure; $UnitOfMeasure ] Set Field [ OrderCharge::BillingFrequency; $BillingFrequency ] Set Field [ OrderCharge::BillingMode; $BillingMode ] Commit Records/Requests Go to Layout [ “Masterfile - Charges” (Charge) ] Go to Record/Request/Page [ Next; Exit after last ] End Loop

Hi, I have a field that I only want to allow data entry into if it is empty. Is this possible? If so, what is the best way? Hal

Great, thanks! I like the first choice, I think. I assume the global fields should be in the same table.