Jump to content

RalphL

Members
  • Content count

    1,744
  • Joined

  • Last visited

  • Days Won

    1

RalphL last won the day on June 27 2012

RalphL had the most liked content!

Community Reputation

2 Neutral

About RalphL

  • Rank
    member
  • Birthday 08/21/1926

Profile Information

  • Gender
    Male
  • Location
    St. Louis, MO

Recent Profile Visitors

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

  1. Use one of the SQL plugins & Filtered Portals. I was able cut out at half of the TO's in one of my solutions using SQL. Look at the FQL section of the Fourm.
  2. ExecuteSQL uses TO's on the graph. If there is a TO for a table from another file it should work. Haven't tested it.
  3. I suggest that you add an Invoice Line Item table. This will give you more flexibility.
  4. RalphL

    Getting Distinct ID's

    Kevin Frank"s latest BLOG has a demo file that includes some custom functions which will produce a string that is properly formatted for use with the IN clause. This might solve your problem.
  5. Try this: Let ( [ $a = system::SEASONYEAR ; $b = $a -1 ; $c = system::CHAPTER ]; ExecuteSQL ( "SELECT partyUID FROM history WHERE seasonYear = ? and Chapter = ? AND NOT IN SELECT partyUID from history WHERE seasonYear = ? and Chapter = ? " ; "" ; "" ; $b; $c ; $a ; $c )
  6. In the ExecuteSQL presentation in the 12 Days of Filemaker12 one of the demos showed the results of a SQL query presented in the web viewer. The blog gives a link to see the presentation and to download the file. http://www.skeletonkey.com/blog/executesql-function-day-10-of-12-days-of-filemaker-12-web-series
  7. RalphL

    Relationship and calculation field problem

    Since you are using FMP 12, you can use the ExecuteSQL function to get the quantity invoiced. Make an un-stored number calculation = ExecuteSQL(" SELECT SUM (c.Quantity) FROM InvoiceDetails c INNER JOIN Invoices b ON c."_kf_Invoice_ID" = b."_kp_Invoice_ID" INNER JOIN OrderDetails a ON b."_kf_Order_ID" = a."_kf_Order_ID" WHERE c.ProductCode = a.ProductCode AND a."_kp_OrderDetail_ID" = ? " ; ""; ""; OrderDetails::_kp_OrderDetail_ID ) in the OrderDetails table. The "'s are necessary because you have field names that do not start with an alpha character. I made a sample file and tried to use your nameing convention. You should be able to just copy and paste into your database.
  8. The numbers are the number of days not covered in first part of the calculation for a set of of days, they do not represent days of the week. Lets review the first line I gave you, 0123455. This line is for the start date being a Sunday. The first number is for the end date also being a Sunday. Result is 0. The second number is for end date being a Monday. Result is 1. The third number is for end date being a Tuesday. Result is 2. The fourth number is for end date being a Wednesday. Result is 3. The fifth number is for end date being a Thursday. Result is 4. The sixth number is for end date being a Friday. Result is 5, even though you don't work on Friday the number of working days increased for work done on Thursday. The seventh number is for end date being a Saturday. Result is 5 because you didn't work on Friday. The next row is for start dates that fall on Mondays. Lets start with the second number in this row. The second number is for end date being a Monday. Result is 0. The third number is for end date being a Tuesday. Result is 1. The fourth number is for end date being a Wednesday. Result is 2. The fifth number is for end date being a Thursday. Result is 3. The sixth number is for end date being a Friday. Result is 4, even though you don't work on Friday the number of working days increased for work done on Thursday. The seventh number is for end date being a Saturday. Result is 4 because you didn't work on Friday. The first number is for the end date also being a Sunday. Result is 5. Continue with this pattern until you complete all 7 rows.
  9. The first part the calculation [5 * Int ( ( EndDate - StartDate ) / 7 )] is the number of working days for full weeks, since you work 6 days the 5 needs to be changed to 6.The second part [Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )] is the number of working days in the partial week remaining. The string [0012345501234544012343340123223401111234010012340] has a number for each of the 49 combination of start & end days. This might be easier to see if the string is broken up by weeks. The columns are the day of the end date and the rows are the day of the start date. End day SMTWTFS 0012345 S 5012345 M 4401234 T 3340123 W Start Day 2234011 T 1123401 F 0012340 S You will need to create a new string based on 6 days & Friday off. The only in common will be the diagonal where start day and end day are the same will be zeros. The first line (Starting on Sunday) would look like this: 0123455 The [7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate )] calculates the position in the string.
  10. RalphL

    execute mySQL

    This has nothing to do with MySQL which is a different database program. You are making an SQL query in FMP, The code you show: ExecuteSQL ( "SELECT fullName FROM people WHERE LOWER(fullName) LIKE '%" & Lower ( GLOBAL::SEARCH ) & "%' " ; " ; " ; ¶) will find every name that contains whatever is in GLOBAL::SEARCH. I don't think that is what you want. You have a wildcard before and after GLOBAL::SEARCH. I would change the where clause to: WHERE fullName LIKE '%" & Upper ( GLOBAL::SEARCH ) & "%' " This will find all first and last names that start with GLOBAL::SEARCH. Leave out the first % and you will get first names that start with GLOBAL::SEARCH. If you only want last names then use lastName in the Where clause. Look at FQL heading for more on using SQL in FMP. Kevin Frank's SQL Sandbox is a useful learning tool and included some custom functions for working with SQL in FMP/
  11. The SQL Plug-ins are for using SQL within FMP, not for ESS. FMP has a method of creating SQL quires without a plug-in see the ESS documentation or help file.
  12. I think you have found a bug in the SQL engine. Teacher C has no duplicate Students, change one of the students or add another to make a duplicate and it works. I also added Teacher D and had the same results.
  13. RalphL

    Best way to create this relationship?

    In the Reptile table record ID is the primary key, you need a foreign key in Medical that is the same type, i.e. number and has the same value as the primary key in Reptile. So add a field to Medical. Now make a relationship from Reptile::record Id to Medical::new field. Set the relationship to allow creation of new records in Medical via the relationship. Set up a portal in reptile with this relationship. There will be an empty portal row use this to create new records in Medical.
  14. Button Dialog.pdfwhen you create a button to run your new script set it to halt the current script. See Attachment
  15. I assume the relationship is Customer ID = Customer ID. This never has to be displayed on layouts seen by users. Now if the user starts in the order table and makes a new record there is no Customer ID in this record, therefore there is no relationship. You need to make a global field to enter the name and use that to search your customer table. I don't think this is a many to many relationship but 2 one to many relationships.
×

Important Information

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