7 posts in this topic
I created a database to track permits by building. Each permit type is stored in its own table. The only link between tables is the Building Name which is the physical location where the permit exists.
v_Buildings (fields include building name, ID, building status, address, city, state, zipcode)
Air (ID, Building Name, Expiration Date, Days_to_Expiration, Type, etc...)
...9 permit types in all, and I will add more in the future
Each of these tables has the same fields in common:
I created a Dashboard layout with the idea that users could select a building name from a drop-down menu and then see all of the permits for that particular building along with their expiration date and the permit owner's name. I am just baffled as to how to do this across multiple tables. I have looked at SQL, Join tables, portals etc...., and I cannot figure out how to aggregate all of this information into one view for users. I cannot even seem to figure out what table should be used for the Dashboard layout. I would love to hear from the community the best and hopefully scalable approach for designing this layout. Thanks in advance from a novice user.
By Ron Neville
Can anyone see whats wrong here:
Let ( [ invoiceID = id ; decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ; SQL = ExecuteSQL ( " SELECT SUM ( b.\"Amount\" ) FROM \"Invoice Data\" b WHERE b.\"id_Invoice\" = ? AND b.\"Type\" = Rental " ; "" ; "" ; invoiceID ) ] ; If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 ) )
There are 2 tables Invoice and Invoice Data. The items added to the invoice can either be a rental, Sale or service item and want to total each type on the invoice layout.. Thank you
Can someone tell me if this query is correct? I'm trying to find all currently open Cases, but only those that belong to a personnel whose 'exclude_from_stats' field is null.
So far this does seem to give me the correct results, however I am a bit uncertain as I have been having problems recently defining criteria in related records. It seems that you sometimes have to resort to different types of joins or you run the risk of excluding/including too many records.
SELECT COUNT (*) FROM Cases C
JOIN Personnel P ON p.personnel_ID = c.User_IDF
WHERE c.Closed IS NULL AND p.exclude_stats IS NULL
By Sam Reicht
I need help with SQL and Running Total (instead of Total).
INV = Invoice table
- INV::Amount ( Amount of Invoice) - INV::DateMonth ( Month of InvoiceDate) - INV::DateYear ( Year of InvoiceDate) DTE = Months table (fixed 12 records) - DTE:Order=1...12) - DTE::CurYear = 2016 Below SQL request works, see my result! But what I would require is a running total, adding each month sales total, see my "Required result" ExecuteSQL ( "
FROM \"DTE\" b
LEFT JOIN \"INV\" a
ON b.\"Order\" = a.\"DateMonth\"
AND b.\"CurYear\" = a.\"DateYear\"
GROUP BY b.\"Order\"
"; " | " ; "0¶" )
My current result as above SQL request
Below is what I require (a Running Total)
I can do this without SQL, but I want to do using SQL and use it for my Charts.
Would appreciate any help. My SQL understanding is basic and I am using FileMaker 14 Adv
I am new to FM, coming to it from a SQL Server/Oracle/Access background. I have created a database with layouts and while there is certainly much more for me to learn it generally is working as I desire it. However, I need to perform a task that I do not understand how it would be done in FM. The example below is not my actual problem but does exemplify my core issue and hopefully is easier to understand than if I described my project.
I have a table of Users, a table of Entitlements, and a join table of UserEntitlements. I want to execute SQL that, for a given User, will read from the Entitlements table and insert rows into UserEntitlements for this user. The query will read from Entitlements, insert a row for every entitlement for this user and, based on data in Entitlements take, will mark some of the entitlements as 'active'.
Ideally, every time I created a new user this SQL would be called (I'd have it as a trigger in a SQL or Oracle database, or I'd have a batch process that ran frequently looking for new users). Basically, as a new user is created I want to create their entitlements profile and activate standard entitlements.
I don't need/expect a full answer - was just hoping I could get pointed in the right direction: is 'scripts' the area I need to explore? Is there a SQL capability to FM? I have not found it if there is.
Thank you for any assistance you can provide,