Jump to content

gilbenl

Members
  • Content count

    25
  • Joined

  • Last visited

Community Reputation

0 Neutral

About gilbenl

  • Rank
    member
  1. gilbenl

    Do I need join(s) in this?

    I have a db with the following tables, with pertinent fields listed: Locations-pk_LocationID, Location, Bed, Service Observers- pk_ObserverID, ObserverName TimeClock- pk_ClockID, TimeIN, TimeOUT, Date Cases- pk_CaseID, Date, Start Time. Delays- pk_DelayID, DelayCode, Duration For a given record, a location can have one observer, each observer can have multiple cases, but each case has only one observer, and each case can have multiple delays (fixed list of delay codes). For the time clock, each observer can have multiple time clock entries, but each entry can have only one observer. Time clock entries do not need a relationship to/don't reference the other tables. Data entry is entirely through a layout referencing the 'cases' table. As a result, when a new record is created through the cases table, a new observer and location record is created. I use a portal to enter delays via the cases table, so for each caseID, I have multiple delay records. The first file shows the relationships I have now (I have removed everything but the tables/fields). The second file contains where I believe I need to put in some join tables. I'd really appreciate some feedback! Examples.zip
  2. I am at the limit of my knowledge on the issue, but some examples that come to mind: -Measures to prevent editing of clock in/out time values, intentional or accidental (IE: Security) -Means of handling folks failing to clock out -Verification of time in/time out. IE: You don't want someone being able to clock in/out in the middle of the night (when they weren't authorized to work). You can check this after-the-fact, but that isn't ideal, or foolproof. I'm sure there are others in the forum who are far more knowledgeable though...
  3. So the database is used by a group of students to document surgical cases. The observers have no need to see other records; they just enter data. Each session, they download an empty DB file, enter their data, and the script sends me a copy of the now populated DB (.fmp12). I then add the new records to the main DB on my computer. Next time around, they just pull a fresh copy of dropbox and repeat. Side note: If you're using this time sheet to track your employees hours for payroll purposes, you may want to consider one of the many pre-made solutions. You can find yourself with a major headache unless you implement significant data verification.
  4. I'm not 100% sure, but I know that I've had problems with InsertCurrentDate and therefore use SetField; Get(CurrentDate). Also, the way your portal is set up may have something to do with it. Not sure if this is the best way to do this, but it has worked for my purposes. See attached. Username is admin with no pass. Now, the question remains as to whether or not your want to have a portal displaying the employees past entries. This is a bit more complicated. However, if you're the only one who will see this info, I suggest creating an admin dashboard. TimeClockExample.fmp12.zip
  5. Show Custom Dialog ["Clock In"; "Are you sure you want to clock in?'] If [Get(LastMessageChoice)=2] Exit Script[] End If New Record/Request Set Field [Observers::Observer; Get (AccountName) ] Set Field [Time Clock::Date; Get (CurrentDate)] Set Field [Time Clock::TimeIN; Get (CurrentTime)] I have a timeclock in my DB and this is the script I have tied to my clock in button. I run it on FM GO on iPhone 4S, 5 and iPad Retina and don't have any issues. How is your timeclock table set up in relation to your DB as a whole?
  6. To answer #2- The purpose of the DB is to collect data on case starts. When the observer arrives in the unit, they input all their cases at once. As delays etc occur, they modify the record as needed, but when finished, they check the "complete" box. When this is checked, it throws a 1 into the complete field. The purpose of the step in question is to check only that day/users records to make sure that they hit complete for every record. If not, it throws them back to a list that shows the incomplete.
  7. This is my first, what I would call, complex script and was hoping to get some feedback on its' efficiency. It is used in a timeclock solution for a filemaker GO "app." It is intended to perform the following actions (same order as in script attached): 1) Verify all records from that date are marked as complete. (eg: Cases::Complete=1) 2) If okay, go to time clock, sort based on "time out," making sure the most recent record is empty. If not empty-->display error, unsort 3) If empty, time out as current time 4) Email copy of entire DB. It performs all of these actions without error, but I'm sure there's a way to make it better. Thanks in advance for the input. Clockout.tiff
  8. This is my first, what I would call, complex script and was hoping to get some feedback on its' efficiency. It is used in a timeclock solution for a filemaker GO "app." It is intended to perform the following actions (same order as in script attached): 1) Verify all records from that date are marked as complete. (eg: Cases::Complete=1) 2) If okay, go to time clock, sort based on "time out," making sure the most recent record is empty. If not empty-->display error, unsort 3) If empty, time out as current time 4) Email copy of entire DB. It performs all of these actions without error, but I'm sure there's a way to make it better. Thanks in advance for the input. Clockout.tiff
  9. gilbenl

    Creating Subset of Value List

    I'm fairly new to this, but I am currently doing something similar so I might have a solution. I would suggest doing the following: Create a table and populate one field with your list of codes. Then, create a number field, "Relevant." Go through your list, and for each one you want to be on the "relevant" list, put a 1 in the relevant column. Relate this table to others as necessary Next, create a filter script that will filter out records without a "1" in the relevant field. In your data entry layout, tie a dropdown list to that list of codes. When prompted to chose values, select use field and select the values. Finally, next to your list, put a button tied to your filter script. When pressed, it will sort out non-relevant codes. This will allow you to have both long and short lists. I think that would work, but I'm a novice.
  10. gilbenl

    Statistics data by week or day

    I've made great progress in my DB thanks to the help I've received thus far, but now I've hit another wall...so, I'm back. For each record, you can have 1-5 delays, as seen here: Delays.tiff You will notice that the delay "codes" are a pair of numbers, with a text description. The field is set to number, FYI. The first digit denotes to which group that delay belongs to. For example, delay #11 is in Group 1 (patient related delays). As I described earlier, some cases have 3 delays, each from a different group, where as some have 3 delays, of which multiple belong to the same group. What I need to do is find an effective way to have FM assign a weighted score to each delay in each case, and then produce a percent total. The long way of doing this can be seen in this excel file: https://dl.dropbox.com/u/1149002/DelayDataExample.xlsx So, for example, for ID_Delay 9, group 9 was 100% responsible. Similarly for ID_Delay 11, there were 2 codes for group 2 and one for group 3 so 66,66,33 was assigned. I need to find a way to make FM do the same thing that was done in that excel file, only automatically. Any ideas?
  11. gilbenl

    Statistics data by week or day

    Edit: May have figured out the root of the issues. Edit 2: Definitely figured it out...relationships click, portals are neat, mind blown...woo! Relationships1.tiff So now I have an empty database with all the same fields, but correct relationships, and a full data base with inappropriate formatting. Is there any way to transfer entire fields between tables, or will I have to create an entirely new DB and custom import the old one, or manually enter all 150 existing records?
  12. gilbenl

    Statistics data by week or day

    I went ahead and tested it by extending it through 100 rows. Seems to respond appropriately, but will keep an eye out going forward. Thanks to your help, I was able to make considerable progress on the DB and it's beginning to come together. Unfortunately, there are two elephants remaining: First is how to effectively quantify the delays. For each record, the observer may enter up to 5 individual delay "codes" in the fields labeled delay 1-5, with one delay per field. Delays are entered by selecting the desired "code" from a drop-down list populated by custom values. The custom values are designated as number fields and formatted as follows: ## Delay Reason. (eg: 11 Outpatient Late Arrival). The first digit indicates the group, and the second digit specifies the exact reason within that group. So, if the leading digit is 1, this indicates that the delay is patient related. 2 is physician related, and so on up to group 6. As I mentioned, the observer may enter up to 5 individual delay codes that are mutually exclusive. They are instructed not to enter duplicates, but there is no measure to prevent it. The result looks like this: DelaysExample.tiff With these delays, I need to quantify them a couple ways. First, I need to count the number of appearances of each delay. I can easily do a summary for each delay field individually (IE: Delay 1) and find out a percent total. My question is how to combine Delay 1-5 and output a unified count without regard to which delay field identical codes are in. For example, if in record #1, Delay 2 is "11 Outpatient Late Arrival," but in record #50 the same code appears in Delay 4, how would I be able to count those? Then it gets a bit more tricky: Recall how the leading digit designates a delay "group." I would like to then assign a weighted responsibility based on group. For example, if Record #1 had only one delay from Group 2-Physician Related, I want FM to put a "responsibility score" of 100 on "Physician" for that record. But, let's say Record #4 had 2 delays: One from Group 1 and one from Group 2. I would then like FM to divide the score between the two groups, 50/50. To complicate matters, there are records where you might have 2+ delays from one group and 1 delay from another. It would then need to assign 66/33. I have attempted a few things to achieve this, but have had no notable success. I have attempted to do a summary for each delay, and a sub-summary for the summaries from all 5 groups. I have also attempted to create a secondary table that combines all 5 delays into 1, which obviously didn't work either. So, I'm pretty much stumped on this one. My second issue is MUCH simpler. I have created a dashboard to display charts of the on time percent (OTP), which looks like this: DashExample.tiff You will notice that there is a drop down menu on the left of the portal. This menu contains all values from the Field "WeekStart," which corresponds to the X-axis of the graph. Although not shown, there is a second drop down menu that contains values from the Field "WeekEnd." Using these two drop downs, I would like to have the ability to select the range of data being displayed on the graph, as well as the results of the calculations displayed in the fields to the right. To do this, I have attempted a couple of approaches. For one, I tried to setup the button to do a simple find. Second, I attempted to do a constrain. Neither worked. I know there is a simple way to do this... I wish I were able to post the FM file itself, but it is being used as a secondary method while I'm building it. So, HIPAA etc etc etc. I have tried to be as clear as possible, but I recognize that this may be a bit confusing. If anyone is willing to take a swing, I'll be around to clarify when needed. Thanks again guys. This DB has grown by leaps and bounds thanks to your help.
  13. gilbenl

    Statistics data by week or day

    Outstanding! Thank you, sir! The website is also a phenomenal resource as well. Just to help out the next guy who comes through looking for this, the actual function I put in is: c_WeekStart=Let ( nY = Date ( 1 ; 1 ; Year(Get(CurrentDate))) ; nY - DayOfWeek ( nY ) + 7 * WeekNumber - 5 ) c_WeekEnd=Let ( nY = Date ( 1 ; 1 ; Year(Get(CurrentDate))) ; nY - DayOfWeek ( nY ) + 7 * WeekNumber - 1 ) Where WeekNumber is a auto assign number field 1 to 52. Output is the Monday and Friday of each week, respectively. Credit for the function and additional resources can be found here: http://www.briandunning.com/cf/1439
  14. gilbenl

    Statistics data by week or day

    Interesting...I thought the method for finding month boundaries seemed a bit too complex, but it made sense. The method you suggest also works and is much simpler, so it sticks. For my own education, could you explain how the calculation you suggest takes into account months with less than 31 days? Is this inherent in the Date( ) function? As for the week, I'm a bit confused. I would like to create a report similar to the months but by week. I assume that would require a table with 52 entries, just as the month required a table with 12. It follows then for each of the 52 Week numbers, I would need a WeekStart= and a WeekEnd, correct? Wouldn't your suggestion only present the current week? See the attached image for an image of the table created for the months: MonthsRecord.tiff
  15. Using this tutorial (http://www.anvildataworks.com/?p=554/) I have managed to produce a series of reports by month. These include basic counts and percents, nothing too fancy. Now, I would like to do the same thing, but display this same data by week or day. The end goal is to have a tabbed dashboard which would display running data by month/week/day, but will also allow specific months/weeks/days to be selected. To achieve this for the months, I created a months table with 12 entries. Each has a DateStart and DateEnd, which are calculated by the following, respectively: DateStart=MonthNumber & "/1/" & Year(Data_Dashboard::g_CurrentDate) DateEnd=MonthNumber & "/" & Case(MonthNumber = 1 or MonthNumber = 3 or MonthNumber = 5 or MonthNumber = 7 or MonthNumber = 8 or MonthNumber = 10 or MonthNumber = 12 ; 31 ; MonthNumber = 2 and Mod(Year(Data_Dashboard::g_CurrentDate) ; 4) = 0 ; 29 ; MonthNumber = 2 ; 28 ; 30) & "/" & Year(Data_Dashboard::g_CurrentDate) This produces a three column table with each month number, the first and last days of that month, which is then used in a series of calculations to give me the stats I need. My question is how to calculate dateStart/End by week with the first day being Monday and the last, Friday? I would also like to do this by day.
×

Important Information

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