Jump to content

Peter Barfield

  • Content Count

  • Joined

  • Last visited

  • Days Won


Peter Barfield last won the day on June 2 2016

Peter Barfield had the most liked content!

Community Reputation

4 Neutral

About Peter Barfield

  • Rank
  • Birthday 04/23/1966

Profile Information

  • Industry
    Self employed
  • Gender

FileMaker Experience

  • Skill Level
  • FM Application
    14 Advanced

Platform Environment

  • OS Platform
  • OS Version
    Win 10

FileMaker Partner

  • Certification
    Not Certified

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. 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.
  2. 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
  3. 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
  4. 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.
  5. 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
  6. 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 rare
  7. 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
  8. 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
  9. 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 th
  10. 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
  11. 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 date
  12. 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
  13. 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.
  14. 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
  15. 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 -
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.