1 2 Previous Next 17 Replies Latest reply on May 20, 2012 1:10 PM by GuyStevens

    Fields

    tays01s

      Title

      Fields

      Post

      I'm still having to convert my thinking from Excel to FM so probably missing the point but:

      1. I need some intermediate ID and calculation fields and be able to view them in Table view. However, I don't want them appearing in the 'front end'. Because a field disapears from Table view if you delete it in Layout, in order to see the field, will I have to create a 'backend layout' together with associated Table, in order to view these fields.

       

      2. As I develop the solution, I'm findings I would like to move a field from one table to another (ie. not copy/duplicate/port, just move it). How do you do this?

       

      Lastly, is there any guide in how to develop equivalent functionality in FM to that in Excel? I don't mean direct conversion, just insight into how you achieve similar ends.

        • 1. Re: Fields
          GuyStevens

          Filemaker and Excell work a little different and require a different way of thinking.

          For your first question:

          What I always do is name my tables witj "Tbl" in front. For instance: TblCustomers.

          Filemaker then automatically makes a "Layout for that table called "TblCustomers"

          If I then want to create a layout for the user to interact with I always make a new Layout and call it "LayCustomers.

          That might sound a little silly but the reason is:

          If you make checkboxes, redio button sets, od dropdowns in your layout and you go into "Table view" you also get those layout items. And they prevent you from seing the actual data that's in the field.

          So I'll create a folder with all my tables, and I create layouts as I need then. That way the users deals with a pretty layout with colors, only the fields he needs to see and handy dropdowns, checkboxes and radio button sets.

          But if I want to see the actual data, I go to the original layout (TblCustomers) and I get to do whatever I want there. I can include, or exclude fields from my view, do sorts, finds, etc. And I can see the actual data that's in the field.

          Also, if you go into table view, on the right top, you can click "Modify" And there you can determine what fields are visible, and in what order.

          So you only need one table (TblCustomers) and based on that table you can create as many layouts as you want, they can be in Form view, list view or table view. And you can include whatever fields you want. You could make a "User" layout and another for yourself. But like I explained, I just use the original.

          Try this example file to see what I mean:
          http://dl.dropbox.com/u/18099008/Demo_Files/BillsMonthly.fp7

          2: Moving files.

          I think you might be going about this wrongly. You don't usually move records or fields from one place to another. It can be done, but I'm thinking you might be using an excell mind set here.

          Give us an example of what it is you are trying to achieve and well see if we can steer you in the right direction.

          3: Excell functionality.

          You can directly import an excell file into a filemaker table. You can create calculations etc. What is it exactly that you are looking for?

          I think it might take a little change in thinking when you come from excell, but I assure you Filemaker can do pretty amazing stuff, so If you hang in there you'll get the hang of it and you'll never want to go back.

          • 2. Re: Fields
            tays01s

            Hi DaSaint,

            Thanks.

            1. So I'm on the right track thinking that it's useful to have a 'backend' Table/Layout from which to extract/use fields in the frontend layout visible to the user.

            2. Excel to FM functionality: What i meant was, is there any guidance doc that explains how you get from FM similar functionality to that in Excel? As you suggest, I realise FM can achieve the same/more, but you have to think in a different way. It's explanations of common functionality I could do with. Eg. A dd in Excel can be populated dependent on another dd choice and itself trigger a Vlookup. I'd like to have explanations of how you achieve parallel functionality in FM.

            • 3. Re: Fields
              GuyStevens

              1.

              Yes, I think you would be on the right track. The table is where you store all information you need from a certain table. For instance. In my example file (BillsMonthly) I have a few calculation fields in the table. They calculate the Name of the month from a date field. And the Number of the month. In my first layout, a detailed view, I don't use these calculation fields, they are thus not visible for the user.

              In my list view I do use one of them. I use the MonthName to display the name of the month as it has been calculated from the date field.

              The calculation would be something like:

              MonthName (DateField)

              That means that if you type 01/01/12 in your date field the calculation in the c_MonthName field will return "Januari"
              Another calculation "MonthNumber (DateField) would return "1"

              I also have that MonthNumber calculation in my table but it isn't visible anywhere. I just use it to properly sort the months in the list view.

              So it's perfectly possible to have a whole lot of fields in your table that the user never sees.

              2.

              I am not aware of a document that compares functions between Exell and filemaker. But if you go here:
              http://www.filemaker.com/support/product/documentation.html

              And you download:


              Then you'll get two pdf documents that show and explain all of the Filemaker Functions (that you use in calculations) and all of the script steps.

              You can use these documents to learn about functions and see examples.

              If you have any specific functions you are thinking about you could let us know and we could tell you what's possible.

              I don't know what dd does in Exell. But it sounds like something to do with the day.
              In Filemaker you also have date and time functions. You can get the current Date, Time or Timestamp. Then you can do calculations with those etc.

              I looked for a second at the vLookup function. And it looks a little complicated. I think you'll find that working in filemaker with related tables if far easier.

              If you ever need a simple example of your solution you can always let me know. I'll whip something up for you and then you can get the hang of filemaker.

              • 4. Re: Fields
                philmodjunk

                a field disapears from Table view if you delete it in Layout, in order to see the field, will I have to create a 'backend layout' together with associated Table, in order to view these fields.

                Did you know that in table view, you can click the modify button while in browse and add those fields back into the view without having them present on the layout when viewed in layout mode or in Form or List views? That said, having a "trouble shooting" layout hidden from the user where you can enter Table view and get a quick overview of the data in a given table is often useful. (I don't call it a "back end" layout as that has a different connation when talking databases.)

                • 5. Re: Fields
                  tays01s

                  Hi PhilModJunk

                  Modify: I hadn't realised that you could get fields back into view with having them in layout; thanks.

                   

                  Hi DaSaint:

                  1. OK

                  2. I'll get to those. Only 2 weeks on FM so I've not got a critical mass of knowledge to string concepts together.

                  Probably needs to be in another post but Re. dd; sorry I meant a drop-down menu. So for example, I'd have

                  i) a radio button to determine 'current or discharged'

                  ii) a dd of names from which to choose a name

                  iii) a dd of treatments dates

                  i) would determine what names populated the dd for ii) and the name chosen in ii) would determine the treatment dates populating iii). Once iii) was chosen it could determine values populating other fields. With my Excel Vlookups they often pulled in values for many, sometimes 100s of fields. Again, I'm pondering what the FM solution to this might be.

                  • 6. Re: Fields
                    GuyStevens

                    Radio buttons, Checkboxes and dropdowns looking up information from other tables are no problem in Filemaker.

                    Automatically filling in of fields based on conditions is also no problem.

                    From the information I have I would say you would need:

                    A table of Patients. A patient has a "Status". Either "Current or Discharged"
                    This could also be automated. If a patient's last discharge date is older then today it means he is discharged.
                    For the rest you add patient details like adress, phone number etc.

                    I don't know how the selection of dates works? Are there predetermined dates?

                    You would need a table for the treatments.

                    The idea in filemaker is that you make a new record in the treatments table.
                    You link this record to a contact from the contacts table by selecting the contact from a dropdown box.
                    This dropdown can be restricted to only show "Current or Discharged" patients.
                    This stores the contact's Id number in the treatments table so you'll get a record like this:

                    TreatmentId: 1
                    TreatmentDate: 01-01-12
                    Patient: 1

                    The Patient field has a relationship to the PatientId field in the Patients table. And therefore you only need to store this patients information once and can then always link to him using his ID number.

                    In the treatments table you can then display all of the patients information directly by putting fields from the Patients table on the Treatment layout.

                    Anyway, if you give me a little more information on how the dates work I'll make you a little example.

                    If you use Filemaker 12 you might want to take a look at this little demo I made for a surgical intern.
                    It might not be anything like what you need, but it might give you an idea of the functionality you might have in filemaker.

                    http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/SurgicalIntern_DaSaintV3.fmp12

                    If you don't have filemaker 12 you could always download the demo.

                    • 7. Re: Fields
                      tays01s

                      Thank you for the detailed info. I'll ponder this and the example given.

                      I've attached my own eg. As mentioned,

                      i) Status would determine what patients populate ii) and patient chosen determines iii) Calc date would determine which specific record is called up.

                       

                      A 'new patient' would be blank whereas a New Calc (currently 'duplicate record') would timestamp whatever record is currently showing.

                       

                      2 queries:

                      1. Best way of overcoming the problem of the Patient field being 'not modifiable' and

                      2.  As mentioned, each record has >200 fields; I assume FM can scroll if I want them on a single layout?

                       

                      Thanks,

                      Stephen

                      • 8. Re: Fields
                        GuyStevens

                        Hey Stephen, I can't see any attachement.

                        If you want to upload something you have to put it online first. Or if you use dropbox you can put it in your public folder and then copy it's public link and paste it here.

                        So if I understand correctly you want to select your patients based on a date?

                        I'll try and paint a picture of how you would nomally go about working in Filemaker. I can't really imagine your situation because I don't know enough about it. But let's try.

                        Because you are dealing with patients it's logical that you would have a listing of patients. These patients can have a status of "Current" or "Discharged."

                        So we are also dealing with "admissions" Patients are admitted and then discharged.

                        For your patients you have 2 views. A detailed view where you can find all of their fixed information (adress, tel. nr., birthdate, sex, ...).
                        This detailed view can also give you a listing of this patients admissions. (current and passed) and you could click on them to see the details of that admission.

                        You also have a list view. Where all your patients are. This list view can have all kinds of search controls on the top. You could for instance choose to only see either the "Active" patients of the "discharged" patients. Or you could search within date ranges of date admitted or date discharged, ...

                        Moving on to the "admissions"

                        The same story: a list of admissions where any one patient can have multiple admissions. They have an admission date, a discharge date. If a patient is admitted but not yet discharged he is "active" If the last discharge date has passed the patient is "Discharged"

                        Other fields here would be fields like "symptoms, possible treatments, ... (I'm not a doctor, I'm just guessing)   :) But these fields are all related to this particular admission.

                        You talk about treatments. I don't know if that is still something else then an admission, again, I don't know enough about your current situation.

                        And you say that each record has about 200 fields. That seems a little unhandy. It's possible, and Filemaker could either scroll or present information in different tabs.

                        But maybe we need to have a more detailed look at what those fields are and examine if we can't come up with a slightly more handy structure for your solution.
                        Maybe you are still sticking to the Exell mind set.

                        Remember that the idea is to split up your information in smaller bits and to avoid repetition.

                        I'd love to make you a little example to show you the idea, but I really don't have anough information about your particular project.

                        So for now whe have 2 tables:

                        Patients
                        Id   -  A number field with auto enter serial number
                        NameFirst
                        NameLast
                        c_FullName  -  A calculation to calculate the full name of the patient:  NameFirst & " " & NameLast
                        Adress
                        PhoneNumber....
                        c_Status  -  A calculation to calculate the patients status.
                        ...

                        Admissions
                        Id  -  A number field with auto enter serial number
                        PatientsIdFk  -  (Foreign Key) A number field and the basis for a relationship to the ID field in the Patients table.
                        AdmissionDate
                        Discharge date
                        AdmissionInformation  -  Here you need to think about all the information you need. Text fields, Dropdowns, Checkboxes, Radiobutton sets. You can use dropdowns to restrict information in other dropdowns....
                        ...

                        That's pretty much as far as I can get. If you can attach your example we might be able to get to a full structure of your solution.

                        1. Best way of overcoming the problem of the Patient field being 'not modifiable' and

                        I don't really understand this one. The patient field on the Admissions table is modifiable. The idea is to enter (trough a dropdown box or from a portal) the ID of the patient so that the particular patient is linked to that admission.
                        The idea being that ptient information and details that are unique (adress, telephone number etc) only exist once in the database.
                        Other patient information that's always changing (the admissions details) are separated, because you can have multiple admissions for one patient. That doesn't mean you have to re-enter all of this patients information. That would be silly. You also don't look it up to store it a second time, because then when this patient changes his adress you would have to go and modify it in every admission record.

                        The patient information is completely modifiable.

                        But don't worry, we'll get there :)

                        • 9. Re: Fields
                          tays01s

                          Thanks.

                          I'd attempted to upload an FM file.......why do they only permit image files? I could upload png's or whatever but it would leave a lot of guesswork compared to giving the forum an FM file.

                           

                          Patient drop-down contains a list from a calc field: lastname&firstname&DOB&sex

                          in order to help ID patients by understandable parameters (ie. serial numbers don't mean much).

                          • 10. Re: Fields
                            GuyStevens

                            If you want to share a FM file you would first need to upload it somewhere and then you can paste the URL here.

                            Or if you have dropbox (www.dropbox.com" you can simply paste the file in your Dropbox public folder and copy the puplic link and paste it here in a post.

                            Then you'll get a link like this:
                            http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/SurgicalIntern_DaSaintV3.fmp12

                            • 11. Re: Fields
                              tays01s
                              • 12. Re: Fields
                                GuyStevens

                                Allright, brilliant.

                                 

                                First problem I'm seing is that you only made one table (EG).

                                Then you made multiple occurences of that one table.

                                That's not really the idea.

                                You need to make multiple tables first.

                                Like I said in my previous post:

                                Patients
                                Id   -  A number field with auto enter serial number
                                NameFirst
                                NameLast
                                c_FullName  -  A calculation to calculate the full name of the patient:  NameFirst & " " & NameLast
                                Adress
                                PhoneNumber....
                                c_Status  -  A calculation to calculate the patients status.
                                ...

                                Admissions
                                Id  -  A number field with auto enter serial number
                                PatientsIdFk  -  (Foreign Key) A number field and the basis for a relationship to the ID field in the Patients table.
                                AdmissionDate
                                Discharge date
                                AdmissionInformation  -  Here you need to think about all the information you need. Text fields, Dropdowns, Checkboxes, Radiobutton sets. You can use dropdowns to restrict information in other dropdowns....


                                I'll start making some changes on yor file and then I'll send it back. That way you can see how it works.

                                • 13. Re: Fields
                                  GuyStevens

                                  Little question:

                                  This file contains only patient information.

                                  What kind of information do you store that is related to the Admission? (or how do you call it?)

                                  There is one field "Ward" I Assume that for this admission this patiënt is lying on this ward. But this patient can have another admission, on another time, on another ward.

                                  Are there any other fields you would like to have?
                                  You said there were 200 fields in your excell. Give me some examples.

                                  Like Admission date, Discharge Date, Symptoms, Treatment, Medication prescribed, ... ?

                                  I don't know what you would like to note per patiënt so if you could let me know that, would be handy.

                                  All the information you can give me now, I can add. Otherwise I can only make a very small example with stuff that I can make up, and that's not going to be interesting for you.

                                   

                                  Also a hit: This calculation

                                  FirstName&Lastname

                                  will give you JohnDoe.
                                  This calculation

                                  FirstName & " " & LastName

                                  Will give John Doe as a result.

                                   

                                  • 14. Re: Fields
                                    GuyStevens

                                    Allright.

                                    After a little bit of playing around I have a little demo to show you my idea of how to do this.

                                    There wasn't a lot of information in your file so I had to make some stuff up.

                                    I hope this gives you a better understanding of how to do things in Filemaker.

                                    Now remember, it's really basic and needs to be greatly expanded to have some real functionality. But I think this is a nice beginning.

                                    http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/Patients_Admissions_DaSaint.fmp12

                                    1 2 Previous Next