I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are:
Client_IDF, Job_time, Job_Date
The best I've managed so far is to find the distinct Client_IDF from all Jobs:
ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; ""; $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line
Or to do something similar but from the Clients table:
ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL.
Is this possible in FM SQL?
By Joost Miltenburg
I find charting confusing. Anyway...
My goal is to do a product year over year comparison on how many times it has been sold given a date.
Two products can be picked and a reference date will be entered. A product has a date in it so it will be unique. That's why I am comparing two products
I found the related table example in the training series and I got it to work. However, the 2016 product comes in the first column and the 2015 in the second. I would like to change that so that 2015 comes first. I've tried sorting the relationship, that didn't work.
I have a DB I'm working on and I'm stuck on something that I think will be pretty simple but I cant seem to get my head around it.
Its for a school, so you have courses,locations,students etc.
Students can take many courses at many locations.
So I have a course layout, with a portal to the participants. This works well apart from I cannot get to open the related record from the portal without it showing me all of the records relating to the student in question. I need to create and reprint a certificate for each student \ course. I have created a button on the portal row to open another related layout up (the cert) to allow them to be printed or emailed, but it always shows me all of the course certificates for the student in question. I have another button on the form for printing all of the certificates for that particular course and that works fine.
I had setup some scripts (shown below) but have gone back to a simple related record button to see if I can figure out whats wrong, I have tried all sorts of changes for hours! and I'm getting no where, any help would be greatly appreciated
edit: Also I realise it shows in the script "records being browsed" but I also did try current record only. Being playing around
I am trying to think through a relational database involving medieval maps as linked to manuscripts.
I have set up the manuscript end of the database. What I am trying to figure out is whether or not I should make the maps in each manuscript relational or not. Each manuscripts comes with on average 21 maps but these can sometimes be missing, so the numbers can fall. Some contain only 1-2 maps and others no maps at all. On the other end there are a few manuscripts that contain 100+ maps and images. There is in other words, no steady amount. If I had to pick a fixed number I would opt for 21 since those are the map manuscripts that I work with the most.
I could set this up as a flat database with container options for 25 maps and info for each one repeated and then fill in those as needed according to each manuscript.
But my better sense tells me that I need to make this relational and set up the maps in a separate database and link them. Then the question is do I need one separate database for all the maps or one for each type of map. So, for instance, there are world maps, maps showing the Mediterranean, maps showing North Africa and Spain, etc. It would be useful to be able to search for all maps of the Mediterranean or the world across all manuscripts but this is not essential because I am also a Lightroom user and I can tag my images to create collections of maps.
My own experience with FM databases varies with the decades. (My skill level should read Beginner not Intermediate) Back in the early days of FM (FM4) when it was owned by Claris (yes, I go back that far) I designed a super complicated manuscript-map database. Then I stumbled over the lack of a big container field for notes so I ended up used printed copies and hand-filling out the data. Life, research, teaching, deprived me of time to work on this again. I bought FM12 ran out of time and now we have FM13 and I cannot afford to buy the upgrade. So I have to do this on FM12 and limp along until FM14 comes out. At which point I will spring for the upgrade.
It sounds odd to say this in 2015 but I really love those delicious container fields that can now even take a pdf. Wow!
Thanks for any advice on this crazy complicated database for a poverty-struck academic who cannot afford to hire a professional to help her out.
Background.Â A record in a table is related by a field to a field in another table.Â To select the related record, I use a dropdown box that shows all the relevant field values that can be selected from.Â The issue is, if the desired value is not there, you want the user to be able to enter a new value, and then automatically create a record for that new value in the related table.
I was having trouble with this issue previously, and decided to take another look at it.Â I came across the following solution, which is worth reposting here.
Here's the relevant section.
Now, for whatever reason, this doesn't work for me unless I add a "Commit Record" at the very top of the suggested script, and then it works like a charm!Â
My previous solution was to force the user to press a "plus" button next to the dropdown to add a new record (via a popover).Â I think this approach is cleaner.