i have a table with 4 fields
field 1 = number result 1
field 2 =number result 2
field 3= percentage result
field 4 = calculation by taking in whichever is lower field 1 or 2 and multiplying by field 3
the question now is how do i script the calculation it should pick up which ever is lower but ignore if field is empty.
i hope i am clear , i would appreciate if someone could help me out with this calculation.
By Eli Walker
I'm trying to use execute sql to populate a field with the sum of all the values in a field of related records. All I get is a ? so obviously something is wrong with my syntax but I cannot figure it out. Any help much appreciated! This calculation is occurring within the 'Collections' table.
Let ( [
query = "
FROM \"Straws_for.entry\" AS S
JOIN \"Animals.to.Collections_join\" AS A
ON \"S.fk_a.to.c.join\" = \"A.pk_join\"
WHERE \"A.fk_collection\" = ? " ;
collection = Collections::pk_collection ;
result = ExecuteSQL ( query ; "" ; "" ; collection)
] ; result )
Also, after some reading on similar threads I see people suggest not using this function in a calc. field... if there's any better way of doing it I'd love to hear it. Thanks for the help, really appreciate it.
I have a set of order forms, in which each order form has X amounts of orders from X amounts of clients. I can summarize with a Summary field the number of orders each client has ordered on one order form (weekly order form) and now I want to make a report that tells me the breakdown from week to week, and quarter to quarter. I can get the report to break apart the weeks by quarter, and it displays each week's correct total, but it won't do a total sum of each quarter, let alone for the entire database.
I've tried summarizing the weekly summary field to get the quarterly amount, either in a new field, or on a trailing sub-summary part. The new field just copies the number from the weekly summary field from the record that is active, and the other way just shows the last above record. Wanna pull my hair out!
I have a feeling that the problem is in how I set up the original weekly summary field, but I just can't see how, or how to make it work.
I have the following:
Table 1 – Expenses
Field A: Date
Field B: Concept
Field C: Amount
Table 2 – Flight Time
Field D: Date
Field E: Flight Time (in hours)
I need a report that will give me the expense cost per hour, i.e. the total of Field C divided by the total of Field E.
Table 1 and Table 2 are not related. What relationship must I establish between them to obtain my desired result?
If establishing a relationship between the tables is not the answer, how else can I achieve the figure I need?
Thank you for any help that can be provided.
I am currently working on a database that will score our company's suppliers.
I have one table that contains all contact information for the suppliers (Suppliers), and another related table that contains the actual score entries (Scorecards). Suppliers are scored on pieces shipped on time and quality reports. We only ever send a scorecard to suppliers based on information from the current year, so I have a field in the Scorecards table for year. The layout I am working with is based on the Suppliers table, with a portal displaying the related entries from Scorecards in the specified year.
Now, where I'm running into trouble is my attempt to retrieve a total for Shipped pieces for a specific Supplier in a Specific year. In other words, how do I get FMP to take many related records, and sum only related records based on data in a third field?