Ron Neville

Members
  • Content count

    13
  • Joined

  • Last visited

  • Days Won

    1

Ron Neville last won the day on September 9 2016

Ron Neville had the most liked content!

Community Reputation

1 Neutral

About Ron Neville

  • Rank
    member
  1. HI Guys and all the best for 2017… Could I please ask for advice on a SQL query i require as part of an availabilities system I am building for a rental database where rental businesses rent inventory to customers for a time period. When a rental job is created it has a start & end date also a start & end time. When I go to my rental item list to add a rental item, next to each inventory item I want to see how many in total I have (own) and how many are available for rent for the date/time range added to the job. Tables Rentals Line_items Inventory Each lineitem added to a Rental has a start/end timestamp so I need a SQL query that will count the QTY in the line_items table for each item for the date/time range of the job. E.g SELECT COUNT qty (Can SELECT COUNT be used in FM?) FROM Line_Items WHERE id_Product = X AND date > X AND dateEnd < Y Any ideas or suggestions would be greatly appreciated..
  2. Sorry about the alignment and was default and didn't realise I could change it Thank you for your response...
  3. Hi Guys, once again thank you for a great resource. This will be simple for you guru's I am get text put into a result field and want to pull out certain data. Here is the result: Messaging Services provide delivery enhancement tools you can apply to your messaging applications at the toggle of a button. Examples include sending messages over long code (10-digit number) when short code messages fail, and automatically distributing high volumes of messages over multiple numbers to ensure speedy, successful delivery.", "status": "queued", "num_segments": "3", "num_media": "0", "direction": "outbound-api", "api_version": "2010-04-01", "price": null, "price_unit": "USD", "error_code": null, "error_message": null, I would like to pull out the "error_code" which is null in the example, also the "num_segments" and "error_message" Thank you in advance... Ron
  4. HI Guys firstly thank you for an amazing resource... I am building a rental system and the period could either be "Weekly" or "Monthly" so say it's set to weekly and the item is $100 per week and when the user goes to create the invoice they are prompted to do a Pro Rata invoice or normal. If Pro Rata I need to know how many days left in the current week, e.g 3 days then I would have to divide the $100 p/w fee by 7 and times it by 3. And the same if its monthly I need to get the amount of days till the end of month, divide the monthly fee by number of days in the month and times it by the number of days left in the month. Companies do this to move into a 1st of month/week billing cycle. Thank you for any help regards Ron
  5. I love the filemaker community and sincerely thank Wim and Barbara for their help...
  6. Hi Wim Thank you and am getting there.. I have what I need in a variable $$SUBCOMPONENTS which has come from a SQL query and is SKU and the quantity: ST0001 1 9315999174634 1 SPA941 1 Could you please tell me how I can loop thru and get valueOne and valueTwo extracted individually? $FoundCount is 3 so will need to loop through adding 3 new records and adding SKU and Quantity. Thank you so much
  7. Thank you so much Wim... Could you please give me help on SQL script and one more question.. How do I get the info out. Say I get the list of ID's added to a variable. Extracting each ID i have no idea about achieving that.. Thank you so much Ron
  8. Hi Guys Been developing with Filemaker since version 6 and stopped at 12. Now with 15 there are many better ways to achieve results. I have an Invoice table and the products table have sub components. E.g a Laptop has a cable and carry bag as sub components. In the old days after adding the main item I would go to the sub components table via a relationship and loop through the records adding them to the order. Just wondering if there is a better way now with V15. Cheers Ron
  9. Thank you Loretta.. You used to help me 10 years ago and will create a relationship.. Thank you all
  10. The main reason I want to use SQL is I dont want to create a relationship via key to get what I need. Could I please ask if someone could write the SQL code to achieve what I require. Above is giving me $0.00 and I don't know enough about SQL yet to solve.. Invoice - Prime key is id Invoice Data - Foriegn Key is id_Invoice Each invoice line item is either a "Rental" , "Product" or "Service" item and "Amount" is the total for the line. I just want to show total amount for Services, Products and Rentals... I thank you so much for your help...
  11. 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
  12. HI Guys I am new to SQL in filemaker and am trying to get SQL Data into variables and hope you can help please. I am running a script trigger after a user exits the "sku" field in a sku added to an invoice. I set the sku into $id and I want to get itemName and price from the products table for that sku and add the result to the variables $itemName and $price. Could I ask what the code would look like?