11 Replies Latest reply on Apr 24, 2012 2:19 PM by KatG

    serial number

    KatG

      Title

      serial number

      Post

       Hello

      I have a basic DB with a one to many relationship. I have created a layout using the parent table as the source table and then I have added portals. Both tables have the ID serial number key.  The portal records are grouped by date and sorted in descending order by the table_ID.  I would like to add a field with an automatic number that would reflect 1, 2, 3, 4, etc. for that date and then when I add records under a different date, it would start again as 1, 2, 3. etc.  I am using a checkbox for this now but I would like to be able to save the step if possible. Thanks I really appreciate all of the help I have received on this forum!  Oh and I am using the trial verion of FP 12, and loving it .... so far :)

        • 1. Re: serial number
          Sorbsbuster

          You could create a self-join relationship from your source table to itself, joined by the date.  Then set the new indexing number to auto-enter a serial number by calculation:

          Max ( SelfJoinByDate::SerialNumber + 1 )

          Best check what happens when you create a date for the first time.  You may have to check to see if it sees any matching date exists, and if not, set the seiral number to be 1.

          • 2. Re: serial number
            philmodjunk

            Be careful if this solution will have more than one user creating records at the same time. In that situation, it's possible to get duplicate serial number values with this method. As a precaution, you may want to set up an auto-entered calcualtion field that combines the date and serial number. Then you can set a unique values validation rule on this field to catch any circumstance where this might happen.

            • 3. Re: serial number
              KatG

              Bear with me...though I have been working on this for quite sometime... this language is foreign to me.  I am the only user so no problems with duplicates. The date filed I use for this is there so that I can keep ithe records straight at a glance....

              You both lost me.... since I am the only user (and probably will be for years to come) How do I create a "self Join relationship"?

              the fielss in that table are    #1)  "ID_DX" (primary key for the child table);

                                                       #2)   "addDxDate"  (date field currently set for auto entry by modifcation... I usually need 4 diagnoses so I have a button set up ( with a script written by an online tech before I found you)

                                                       #3)   "Dx#"   (Text field with a check box  1,2,3 ....)

              Do I need to create another field to calculate some combination of these fields? How do i create a self join relationship? Calculations are difficult for me... one space where it shouldn't be screws up the whole thing.

              I did learn how to create a calculation text field in a previous post... you both commented as well as DaSaint... Thanks again!

              but Indexing and validation rules are new and of course mystifying....could you be more specific? If you are ever in Vegas, I owe you a beer! :)

               

              • 4. Re: serial number
                Sorbsbuster

                Now I'm unsettled: do you mean that the first record created on the 19/4/2012 (I'm Irish) will be serialised as 1, and the next record on that date will be 2?  Or do you mean that for each primary record created, its child records will be 1, 2, 3, 4 - no matter what date they are created?

                One needs a self-join by date only.  The other needs a self-join by the primary key.  And are you serious about the date?  If a patient had a second diagnosis coincidentally on the same date, should it not get another serial number too?

                ------------------------------------------------

                In Manage Database, on the Relationships Graph, duplicate the Parent Table.  Call the new 'table' (strictly, a 'Table Occurance', or TO):

                parentTable _SelfByDate  (Seriously - just change the 'Parent Table' bit to be the correct table name.)

                Now create a new relationship (button bottom left-but-one) and choose the Parent Table on the Left Pane and the parentTable _SelfByDate in the right pane.

                Create a relationship between them using the Date field in the left pane, and the same field in the right, using the = connector.  Now every record in the Parent Table can see every other record in itself that has the same date.

                Set the auto-entry of the calculation to be as I suggested, and each entry will serialise by 1 each time throughout that day.

                BUT: I'm not sure that is what you want...

                (I also wonder why you auto-enter the addDxDate by 'Modification', rather than by 'Creation', but if it works for you, that's fine.)

                • 5. Re: serial number
                  KatG

                   

                  Sorbsbuster : Now I'm unsettled: do you mean that the first record created on the 19/4/2012(I'm Irish) will be serialised as 1, and the next record on that date will be 2? Or do you mean that for each primary record created, its child records will be 1, 2, 3, 4 - no matter what date they are created?

                        Kat:                   I think the second thing....   I want a "Label"  to begin with  the number 1 for each record that has that tracking date to be auto entered ( I have a separate field for this using 1 2 3 in a checkbox set. ) So each patient would have a tracking date and diagnoses labeled 1,2,3

                  Sorb:  One needs a self-join by date only.  The other needs a self-join by the primary key.

                   

                                  Kat:  Not sure about either of these...

                   

                  Sorb: And are you serious about the date?

                   

                         Kat:   Yes but I am not explaining it effectively. The date is only for

                            me to know I am entering into the correct row. I had trouble

                           with the portals in the beginning and this was the easiest

                            solution I'm sure I don't approach any of this like a developer...

                   

                  Sorb:  If a patient had a second diagnosis coincidentally on the same date, should it not get another serial number too? 

                   

                     Kat:       This is what I am trying to do... albeit automatically...Maybe I shouldn't have called this a "Serial Number" Maybe a Number label?  for every patient there is an encounter (with a Date of Service but disregard that for now) and for every encounter there are 4 diagnoses (for the sake of argument) and the "addDxDate" is only for me to keep straight the record I am working in. It helps me sort, I use the scroll quite a bit to review the previous information. I am trying to give the 2nd and 3rd and 4th etc, diagnoses a number "label" without having to have the added step of clicking on the "Label"  in the checkbox set. It is helpful for me to "add" the record rows all at once and then copy paste or enter the data into the fields as it comes, I have to go between patients sometimes I can't complete one and go on to the next

                   

                  Sorb:  In Manage Database, on the Relationships Graph, duplicate the Parent Table. Call the new 'table' (strictly, a 'Table Occurance', or TO):

                  Kat:  Did you mean the "parent" table or the table that has the field I am working with? ( my parent table is "Patient" and the child table is "Diagnosis" )

                  parentTable _SelfByDate (Seriously - just change the 'Parent Table' bit to be the correct table name.)

                                         So should the TO  be       Patient_SelfJoinbyDate  ?

                           or if it should pertain to the child table then

                                                                              Diagnosis_SelfJoinbyDate  ? or Diagnosis_SelfJoinbyAddDxDate because that is the name of the field?

                           I duplicated both tables and can create the relationships but not the calculation I tried many combinations butmessage "specified table not found" ....comes up every time. I made new field and called it "test" set it's type as calculation

                  Max (Diagnosis_ SelfJoinByAddDxDate::SerialNumber + 1 )   using the name of the date field?

                  but I'm thinking the "serial number" part of this is wrong ....wouldn't this need some way to tell it to always start at 1 when it sees the date change and +1 for the additional similar dates?

                  I haven't gotten past duplicating both tables and 'I'm not sure which one I should be looking at.

                  Thanks again for all of your help!

                  • 6. Re: serial number
                    Sorbsbuster

                    Are you trying to:

                    - create a table of Patients, with one patient per record.
                    - create a table of Encounters, where one patient can have many encounters
                    - have a table of Diagnoses, one diagnosis per record.
                    - Each Encounter can have many diagnoses.
                    - on the Patient layout see all the patient's 'standing details', with a small portal listing, in reverse chronological order, their records of Encounters and another, if you want, of their Diagnosis records
                    - click on any of the portal rows in the Encounter or Diagnosis listing and go to a detailed screen of that record
                    - either from there, or back on the Patient's record, add another Encounter or Diagnosis for them

                    You never mentioned 'Encounters' until now, and they've never been mentioned in your Table Structure, so I'm not sure if I've surmised correctly the structure of your data.

                    I've kind-of missed the purpose served by this serial number thingie.

                    • 7. Re: serial number
                      Sorbsbuster

                      If that guess is near-enough right, try this:

                      http://www.4shared.com/file/2uGCNwrv/PatientRecords.html


                      • 8. Re: serial number
                        KatG

                        Damn You're good! I was trying to simplify the descriptions but I guess just furthur confused the situation. I rearranged these tables so that the information that doesn't often change is grouped together and shows shows up  without having to change it... it is the last entered record in that particular portal. I'll make a long story longer :)  I haven't yet mentioneded my wound table... a patient can have many wounds and I have those "labeled" with my checkbox number as well...when I sort in descending order I know at a glance that this patient had 3 wounds and the next one had 6 wounds... portals with many rows take up too much space so though I have the last tracking date shown, the "thingie" helps refresh my memory and gives me a running tally, so to speak. the biller needs things to be in order of the priority I have assigne dot each diagnosis, I get that each subsequent record in in order... but there are times I need to change the order of things I can copy paste the wound Dx from tht calcualtion field and paste it into the rows I have entered and it doesn't necssilary have to be the first row. I need to change the order of the diagnosis, more often than not  

                        I have a tabbed layout...( I call it the pt's chart)  I "Find" the existing patient or enter in the new ones, I add daate ofr service and then I search the date of service so I have those records open. There is demographic information that typically doesn't change during the current addmission but I need to display it on each encounter progress note. I was duplicating those records in the past but it was a pain.... I am just trying to make this as streamlined as possible and save as many steps as possible. The encounter table houses the bulk of what I need to gather but the wounds each need an individual assessment. I am trying to design this to use on Filemaker Go on iPad and I have found I am too addicted to my laptop touch pad mouse and typing on an iPad is not the easiest thing to do after 20+ years of PC... So Check box sets are my friend! and the calculation text fields that you and DaSaint and Phil helped out with last week have made this a much nicer format to work with. 

                        There are times when the wound is a diagnosis in and of itself and there are times when the wound is a symptom of another diagnosis, with that in mind and the fact that the number of wounds doesn't always equal the number of diagnoses I thought I needed different tables. I played around with the layouts and the info I need to see is displayed, but only after I learned the hardway be misplacing data into th wrong rows because I didn't realize I had opened anothr row. I thought I should have used "join" tables but I wasn't sure how to set that up... this way actually works fine so I didn't go any further. The master or Parent table is the "Patient" and it has a one to many relationship with all other tables.

                        Is what I want to do with the "thingie" possible? When a new row is opened with a new tracking date a 1 magically show up in that filed and when I open a second row with the same tracking date a 2 magically appears.... and so on.

                        I am learning .. slowly but surely, I know what I want filemaker to do for me, I just have trouble with the "lanfuage" among other things:)  I haven't even tried to write a script yet! Yikes! Thanks again, oh and btw.. I'm half Irish! Thanks again!

                        • 9. Re: serial number
                          KatG

                          I'm loving all these fish while yo teahc me to fish! I am downloading the DB example you sent and will kep you posted. If you're ever in Vegas I owe you a beer... Thanks!

                          • 10. Re: serial number
                            Sorbsbuster

                            We have a database with many patients, each of whom has many wounds, and each of those wounds will have many treatments.  On the patient's record you can see the the list of wounds in one priority (open wounds to the top, healed to the bottom and greyed out, open wounds at the top sorted by seriousness.  Treatments of each wound displayed in reverse chronological order.)  So there is nothing in any of that that FM doesn't handle with ease.

                            "when I sort in descending order I know at a glance that this patient had 3 wounds and the next one had 6 wounds" - sorry, don't see any necessary connection between the start and the end of this sentence.  You can display a count anywhere, really, and no sort required.

                            "the "thingie" helps refresh my memory and gives me a running tally" - now I'm totally lost.  I can't see any connection between records being identified with a serial number and seeing a running tally.  Unless you mean that you are effectively counting the previous records yourself, and then entering what the new running count of that sub-set will be?  Oh boy...

                            "I Find the existing patient or enter in the new ones, I add date for service and then I search the date of service so I have those records open. There is demographic information that typically doesn't change during the current addmission but I need to display it on each encounter progress note. I was duplicating those records in the past but it was a pain"  - 'duplicating records' has the smell of Poor Design about it.  Manually searching sounds like an unnecessary waste of time.

                            "So Check box sets are my friend!" - Check boxes are brilliant.  In the right place, for the right purpose.  And I don't think this is either.


                            "Is what I want to do with the "thingie" possible? When a new row is opened with a new tracking date a 1 magically show up in that filed and when I open a second row with the same tracking date a 2 magically appears.... and so on." - it's my fault, but I just can't see what service you want this doohickieSerialCheckBox to perform.  At least, no service that can't be performed much easier another way.

                            • 11. Re: serial number
                              KatG

                               

                              We have a database with many patients, each of whom has many wounds,

                              Yes (may have 1 wound or may have 10 , each is different) and the same holds true for diagnoses.

                               and each of those wounds will have many treatments.

                               Basically, Yes

                               On the patient's record you can see the list of wounds in one priority

                                "priority"  ?  Do you mean portal display?

                                (open wounds to the top, healed to the bottom and greyed out,

                               I don't know how to do the "greyed out" I have a "Status" field "Active; Resolved" and this is how I keep them straight.. I' all about cutting out steps, I'm trying to learn the better ways.

                               open wounds at the top sorted by seriousness.Treatments of each wound displayed in reverse chronological order.)

                               Not by seriousness or treatment but by what I addressed at the last visit.

                               So there is nothing in any of that that FM doesn't handle with ease

                              Thank the Developer Gods! Which is why I bagged Access..(What a nightmare) and took on this project. My problem with medical software (apart from the $$$) is the "language" barrier between the  Developers and the Providers, they all tout that they are "customizable"... not for what I need.

                              "when I sort in descending order I know at a glance that this patient had 3 wounds and the next one had 6 wounds" - sorry, don't see any necessary connection between the start and the end of this sentence. You can display a count anywhere, really, and no sort required.

                                The portal is formatted to "sort" by the primary key of that table in descending  order and set to "display" 4 rows. If I set it for any more rows to display it extends off the screen and I have to scroll. I use a tab format, scrolling is annoying.  I guess what I am in need of is a "count" field.

                              "the "thingie" helps refresh my memory and gives me a running tally" -now I'm totally Lost.

                              welcome to my world. :)

                              I can't see any connection between records being identified with a serial number and seeing a running tally. Unless you mean that you are effectively counting the previous records yourself, and then entering what the new running count of that sub-set will be? Oh boy...

                              "Oh boy" is right. This is what I am trying to avoid with what I called a "serial number" It is looking like I need a "count"                     

                              "I Find the existing patient or enter in the new ones, I add date for service and then I search the date of service so I have those records open. There is demographic information that typically doesn't change during the current addmission but I need to display it on each encounter progress note. I was duplicating those records in the past but it was a pain" - 'duplicating records' has the smell of Poor Design about it.

                              It stinks! Though I am probably the "Poor Design Poster Girl" I realize this isn't working in the efficient way that it could so I am trying to explain what I need, I don't understand why I need to justify the reason for needing it...I know you are only trying to help... suffice it to say that my practice is highly specialized and very unique, I do not work out of an office, I provide a specialized consultation in Facilities and I also make house calls. I don't have an exam room , I go from room to room, I have no support staff, I am a one woman band! (yes, I march to my own drummer) I also should clarify that I don't have the luxury of seeing one patient at a time and documenting everything in order and then moving on to the next pt... So having the Pts  I am planning to see on that day "open" allows me to"juggle" so to speak.  I know what I need to see when I need to see it and this database is slowly evolving thanks to all of you and your help.  

                              Manually searching sounds like an unnecessary waste of time.

                              I'm not understanding you here. I have to start somewhere. I wake up. I open the data base. I am presented with a list of patients.  I have to "find" a patient. How else do I do that without typing in the "name" field in find mode? If that name isn't found, then I need to enter data for that new patient... manually, no? When I have found that patient, how else do I add a new record? Am I mistaking your meaning of "manually"? Do you mean using buttons and scripts instead?  I'm all for it but just one thing at a time....... I am trying to understand the rationale for the design. I did have an epiphany when I rearranged the tables and that was beginning to make sense...I mentioned Join tables before and that is probably what I should do but I have to be able to understand it and know I won't misplace the data.

                              it's my fault, but I just can't see what service you want this doohickieSerialCheckBox to perform. At least, no service that can't be performed much easier another way.

                              the check box let's me "tap" the screen on iPad. It saves steps of having to tap the field, scroll the dropdown list or begin typing to auto enter... with the check box and the text calculation field you all helped me with last week I have created almost a "paragraph builder" It has helped in many ways.  I am using the calcualtion text fields in the "report". I'm sure I'll run into pros and cons along the way.

                              I am looking for the easier way.  I am ineffectively explaining "why" I need it.  I do appreciate your willingness to understand, I am just trying to learn the language.

                              So How do I make a "count" field?  I'll try it and see if it doesn't do the job.  I wasn't able to download the file you sent via 4share, not sure why but I tried 3 times.