Peter Barfield

Thanks Wim and Comment. I have taaken on board all your points and you have cleared up my mind in relation to calculated fields and SQL I just didnt differentiate the script from the calc field to my mind they were both calcs thus the confusion in my thinking now I understand. Again thanks for the tip on hard coding. Taken On Board and will do. Cheers and many thanks for everyones imput to my question. I guess the day you don't learn anything is the day you havent tried too hard.

Thanks Comment. I Think I was on th4e right ttrack with my comment regarding giving the SUM column a name. Thought I read somewhere that you could n ot hsve GROUP BY and ORDER BY in the same statemnt as they were essentially the same thing ( I was wrong on that) I hear you on your note regarding protection a great point and one I will work with. Thanks for the link to the quotes function i will also look at that. Cheers and thanks for the help. The learning curve goes on. One more thing that i was pondering on in the car whilst driving in relation to using Execute SQL in calculation fields. My question is if you dont use them in a calculation field then where wold you use them aside from a script? Isnt that essentially a calculation as well? I'm just curious. Cheers again.

Thanks La Retta, appreciate the feedback. You indeed are correct in the handling of credits etc. You have made me think about the best way to recieve an update for a credit applied after the month in respect of the dashboard and the data it returns. OK Im learning that despite what I have been told is not quick in reality particularly, if you have a number of calculations on one screen as per a dashboard. The benefit for me in this instance is the dashboard does not require a lot of relationships made. The decrease in speed is a small trade off. You are also correct the help on this page is awesome. particularly when you are chating un naviagted waters. I actually like comment in that the solution supplied means you have to think a little for yourself where possible. In this instance I have discovered a coiuple of quotations missing. Now all I have to do is work out how to Rank them from Highest to Lowest it appears though that Group By when used makes Order By Redundant thereby I cant Group BY an Aggregated field although maybe I have to SUM AS and give the column a name by which I can Group BY. Still working that one out.

Comment, Thanks for the reply. As per your hint - I did actually just do your calc i.e. not have the InvoiceMonth and InvoiceYear (as I thought this might be the problem) in it it was purely as you returned the calc albeit, I put it into my calc how I write them. It still returned a ?. AI said I am new to SQL is it something in my Syntax or how I use the /" for names etc? I just do not know or understand where it is falling down when your one works for you. I would really like to know so I can learn from my blasted mistake I am guessing it is something really simple but what I dont know.

Comment, I really dont know what the hell I am doing wrong here I have put your calc into my format Let ( [ ReturnSub = "\n" ; SQLResult = ExecuteSQL ( "SELECT \"Customer Name\", SUM(\"InvoiceTotal\") FROM \"Invoices\" GROUP BY \"Customer Name\" WHERE \"Invoice Sub Total\" > 0 AND \"InvoiceMonth\" = ? AND \"InvoiceYear\" = ? AND (\"Job Status\" <> 'Giveaway' OR \"Job Status\" IS NULL)" ; " " ; "|*|" ; 10;2019 ) ] ; Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ) I hard coded the month and date and followed your example for the WHERE Statement. Also GROUP BY was returning a field not found error until I moved it above the WHERE statement. I assume (probably should not do that) that is correct. Now it just calculates the dreaded ? Not sure where I am going wrong.

Comment, Thanks for the help. Yes you are right that in a calculation field it is slow. I wouldnt say very slow however, it is slow thanks for the tip on text not number not sure why thats the case but obviously, it has some bearing.. The table in the sample was a quick knock up for the purposes of trying to get this to work. You are correct the Tax can change and is actually a lookup field in the actual DB thereby that problem is solved. Once again thanks for the help. Wim, Thanks for the reply. You make perfect sense in that the previous month rarely changes unless say there is an error on the invoicing side or a dispute on charges which may take sometime too resolve so credits are applied after the fact. Other than that they don't change. So I will look at a different approach for that aspect. As for the current month they do want current "up to the minute" data so recalculating is the only way forward as I see it.

Thanks Comment. I have dummied up a db (attached). Take your point on the 0 being in quotes and the reason it is there is more a secondary check to ensure nothing else in the invoiceing gets picked up. I'm not sure on the DISTINCT however, I will assume that select only has the same efffect when I am trying to SUM a total as such. (Hope, That makes sense) as I said I am totally new to SQL have done some reading but just cant seem to get it to work even though some examples appear to show Im doing it somewhat right. SQLTestDB.fmp12

Olger, Thanks for pointing that out have corrected and still get the dreaded ? I guess my point is what i am trying to do even achievable the way I am approaching it or should I be taking a different approach baring in mind that from what I understand FM SQL has its limits. Cheers

Well, I'm new to SQL and have used it in it's basic select mode. Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great. I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION in filemaker? Listed is my SQL Statement that just returns a ? Which I understand to mean a syntax error however, being a SQL "Virgin" any halp would be appreciated. I know I am able to do this natively through filemaker however, as I said this is for a dashboard that uses data from a number of tables. Here is my calc that is obviously wrong. Currently working with Filemaker 15 Advanced. Let ( [ ReturnSub = "\n" ; SQLResult = ExecuteSQL ( "SELECT DISTINCT (a.\"Customer Name\"), SUM(a.\"InvoiceTotal\") FROM \"Invoices \" a WHERE a.\"Invoice Sub Total\" > ? AND a.\"InvoiceMonth\" = ? AND a.\"InvoiceYear\" = ? AND a.\"Job Status\" <> ?" GROUP ON a.\"Customer Name\" ASC; " " ; "|*|" ; "0" ; Month(Get(CurrentDate))-1 ; Year(Get(CurrentDate)); "Giveaway" ) ] ; Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) )

Got it. I didn't realise that the variable could start with a minus and it would still work. My wife often saids to me that I over think things and don't see the obvious. This is a classic example. Thanks for your help

Comment, I have attached a screenshot of the script in question. As I said going forward in days via your script is a pearler and works without flaw. Just cannot seem to get it to go backwards in days to show the past. As you can see I have imitated your script added the variable $j (I used J as it was the next after i which you used) and created a Global variable same as you did however just threw a 1 before the final letter to give it a differentname obviously and changed the Weekview Interface _g_selectedDate to -1 instead of +1 (my thinking is this would go backwards in dates not forwards. The only thing I did not alter (or add in was the last line (line 19) i moved your code to the end and so did not perform that after the first loop. ( i did however try with it replicated although that did not work either) maybe I am not fully understanding the script although I think I do. Cheers

Comment, I will do just that however, at the moment I am not able to access my computer and will be on the road for a 6 hour drive home. When I get home I will post it. Cheers

That works a treat. Only 1 issue and I have played around with the script but I cannot get it to show the day previous days unallocated. If I follow the script I can see what it does so I created another variable similar to yours but calculated to go gdate - 1 however that falls flat and sets the field at the end to about 10 days prior to the current Gdate any ideas on where that might be failing? Other than that I am just about completely happy. I have used script triggers on field modify or exit depending to refresh and show everything correctly. once again thanks for your input.

Comment, you have completely got me there. how would 1 go about gathering the list by scripting? Do you have to loop through the records from the plant unassigned table? Or am I thinking this through all wrong?. How do you filter a portal via script? I know I can filter a portal from the portal setup but I am not aware how to do that via script. Sorry to ask so many questions once I get a grip on it I am sure i will be fine. also in one sense the assigned jobs are there I just have to have a way of building the list by looking at the global Date + 1 day, 2 days etc. as the assigned plants come from a relationship with the jobs table based on dates I thought I might be able to tap that information by virtue of the above mentioned criteria. Maybe I am clear as mud again in my explanation if I am I apologise.

Just the unassigned plant for those days Comment I received a notification from you that does not appear here for some reason you said comment said: I would try converting the last portal to a filtered one, based on a relationship using the x relational operator. Then just add more portals and change the filtering expression. That would save you the need to define 6 global fields and 6 relationships. With only a few jobs per day, and a fixed amount of plants it should be fast enough - but you need to test this for real. I have created a relationship of x from the unassigned plant to a TOC of that. Is that what you meant?