11 Replies Latest reply on Aug 30, 2011 7:31 PM by carolynhaywood

    Please help! Portal/relationship question  new info

    carolynhaywood

      Title

      Please help! Portal/relationship question  new info

      Post

      I have narrowed my problem down but I can't find a solution.

      On portal billable time based on layout billable time, I add a new record.  To add additional info you click on a "D" button which takes you to a second tab with a portal based on billable timeselected.  On this portal, I have 4 fields; type, section, code, and description.

      I enter in the field type.  everything okah.

      return to first tab and enter in a new record.  New record in billable time is created but when I go to the second portal and click on field, it changes the field type in the billable time record in the layout on both new entries.  If I go back to the 1st record and change the field, again, it changes the field in both records.

      Nothing else changes.

      The relationships are as follows:

      Billabletime = billableid; jobinformation = bilableidselected.

      CodeSectionTypeSelected = sectionid; jobinformation = sectionidselected

      codesectiontypeselected = type and billablieid = codesectionselected

      codesectionselected = code and bilableid = codesectionselected 2

       

      The first portal is based on table billable time

      The second portal is based on billable time selected.

      The billabletimeselected and sectionidselected are global fields

      What am I doing wrong?  

      Everything I have tried always ends up like this, with the new record field type replacing the old.

      Thanks,

      Carolyn Haywood

        • 1. Re: Please help! Portal/relationship question  new info
          philmodjunk

          I think you have these relationships:

          Billabletime::billableid = jobinformation::bilableidselected.

          CodeSectionTypeSelected::sectionid = jobinformation::sectionidselected

          codesectiontypeselected::type = billablieid::codesectionselected

          codesectionselected::code = bilableid::codesectionselected 2

           

          On portal billable time based on layout billable time,

          That statement seems a contradiction. In Layout Setup, what exacly is listed in Show Records From? In Portal Setup, what exactly is shown in Show Records from? Perhaps your layout is based on JobInformation and the portal is based on Billlabletime?

          but when I go to the second portal and click on field

          And what do you have selected in Portal Setup | Show Records From for this portal?

          And please tell us more about codesectiontypeselecte and condesectionselected. What are they (different tables or occurrences of one of the other tables?) What is there purpose here?

          • 2. Re: Please help! Portal/relationship question  new info
            carolynhaywood

            Here's what I have now:

            Main table = Job information

            Billabletimeselected = billableid + jobid = job information billableidselected + jobid

            Layout "job" based on Job information

            Two tabs on layout.

            Tab 1 portal is from table billabletime

            Tab 2 portal is from table billabletimeselected

            Each record is related to the other by a D button with the script 'set field job information::billableidselected;billabletime::billableid'

            Table CodesSelected (from table Codes) = sectionid + jobid = job information sectionidselected

            Table CodeTypeSelected = sectionid =billabletimeselected sectionid

            Table CodeSectionSelected = type =Table CodeTypeSelected

            Table CodeCodeSelecteed = section = Table CodeSectionSelected

            Portal 2 has the fields Type from table CodeTypeSelected (Value list TYPE from CodeTypeSelected (type) ; all values), section from table CodeSectionSelected (from Value list SECTION CodeSectionSelected (section); from records related by CodeTypeSelected; Code, and Description from table CodeCodeSelected (from Value lists CODE and DESCRIPTION CodeCodeSelected (code/description) from records related by CodeSectionSelected.

            The purpose of the Codes Tables is to form conditional value lists to narrow down 300 records by type, section, code, and description.

            The problem with this structure is that new records have to be created for section, another for code, another for type, and another for description so there isn't one unified record.

            I disconnected the relationship between CodesSelected and job information and between CodesSelectedType so that the three tables are alone.  Then I put fields for type, section, code and description from billabletimeselected on Portal 2 with the appropriate value lists applied.

            The values for type pop up but the values for section, code and description say no values defined.

            What in the world can I be doing wrong?

            Thanks,

            Carolyn

             

            • 3. Re: Please help! Portal/relationship question  new info
              carolynhaywood

              The last way I have the table and relationships works as far as creating unique records.  There is no dublication or overwriting.  But the fields on the portal billable time selected will not pop up.  Only the first one, type works. when I select the type, the section field says no values defined.

              These are fields from billabletimeselected on portal billabletimeselected.  The relationship between the three codes tables is set up correctly but these tables are alone - they are not connected to anything else.  The value lists are correct in their relationships.

              • 4. Re: Please help! Portal/relationship question  new info
                carolynhaywood

                New info.  The only unusual thing I see is that on the codes layout (from table codes) the field section has a yellow dot insead of a green dot.  Field section is from table codes just like all the other fields but this in the only one with a yellow dot.  

                What does the yellow dot mean and why is it not green like the others?  When I look at the table, there is nothing different about section from the other fields.  It is a text field, indexed.

                 

                Also, it tried connecting the three tables to table jobinformation via table codes selected by code id  and code id selected but nothing different happens.

                • 5. Re: Please help! Portal/relationship question  new info
                  carolynhaywood
                   I deleted the section field from codes and created a new one.  It is now a green field. I am in the process of changing all records that might be affected by this change. I have set the three related tables (codetypeselected, codesectionselected and codecodeselected back to no relationships with any table except that they are related to each other by type (first 2) and section (table 2 and 3). The fields on the portal billabletimeselected are type,section, code, and description and are from the table billabletimeselected.  Each field is assigned a conditional value list based on the three related tables When I create a new record, the field for type pops up okay - you can select a type and it does not duplicate or over write the  previous records.  However, the fields for section, code, and description say no values found. I have verified that the conditional value lists are using the correct tables and fields. I have verified that the fields are assigned to correct value lists. Can any give me any advise, please.  I have to migrate the data from the old database to the new one this weekend but I have to have this problem solved.  It's got to be something simple I am overlooking. thanks, carolyn
                  • 6. Re: Please help! Portal/relationship question  new info
                    philmodjunk

                    I'm afraid that I can't follow your description of your relationships.

                    Please include table occurrence names (the names of the boxes in manage | Database | Relationships) with the fields that match to each other.

                    I can't tell what this means:

                    Billabletimeselected = billableid + jobid = job information billableidselected + jobid

                    They read to me like a combination of calculations and relationships which just doesn't make sense...

                    • 7. Re: Please help! Portal/relationship question  new info
                      carolynhaywood
                       Table Jobinfomation is main table. table BillableTimeSelected is related to job information in two ways: 1.  Billableid = billableidselected 2.  Jobid = jobid Table Billabletimeselected is derived from table billabletime Table codetype (i'm leaving off the selected because it no longer matters) is related to table codesection by field 'type'. Table codesection is related to table codecode by field 'section'.  these tables have no relationship to any other tables.  They are derived from the table Codes. Value lists are based on these tables 1.  type - table: codetype - field type - all values 2.  Section - table codesection - field section - values from table codetype 3   Code - Table codecode - field code - values from table code section 4   Description - same as code Tab 1 Portal billabletime is on layout jobs which based on info from table job information and contains info such as employee,date,level rate total, etc tab 2 portal billabletimeselected contains fields: 'type' 'section' 'code' 'description' these fields are assigned to the conditional value list which I listed earlier. each record on Portal billabletimeselected is related to a record on portal billable time.  you get to portal billabletimeselected by clicking a button that takes you to that record. I can create a new record but when I go to portal billabletimeselected, the only field that works is "type", all the others (section, code, description) say no values found. It is a unique record and relates to the correct record on the first portal. Why are the other fields not working?  There are only two types 'tf' and 'ec'.  Choosing ec should bring up all the records in section that are ec. but it doesn't work. Thanks for any help or suggestions you can give, Carolyn Haywood
                      • 8. Re: Please help! Portal/relationship question  new info
                        carolynhaywood
                         Sorry that the above is all one paragraph.  I did not write it that way, the computer did.
                        • 9. Re: Please help! Portal/relationship question  new info
                          philmodjunk

                          Table occurrences are the boxes found in Manage | Database | relationships. Knowing their names along with the fields used to define the relationships linking them is crucial to understanding what you have here.

                          1. Billableid = billableidselected

                          Can be documented as:

                          JobInformation::Billableid = BillableTimeSelected::Billableidselected

                          The names to the left of the :: should match the names of the occurrence "boxes" in Manage | Database | Relationships and should then match the "Show Records From" names in layout setup and portal setup.

                          Is this what you have?

                          2. Jobid = jobid Table Billabletimeselected is derived from table billabletime

                          Sorry, but I can't make any sense out of that. JobID from what table occurrence matches to Jobdi in Billabletimeselected or BillableTime?

                          Perhaps you have:

                          JobInformation::JobID = BillabletimeSelected::JobID

                          Where Billabletimeselectec is a different occurrence of BillableTime.

                          but I'm not at all sure that this is the case.

                          To understand the role of table occurrences in FileMaker databases, you might want to read this thread: Tutorial: What are Table Occurrences?

                          • 10. Re: Please help! Portal/relationship question  new info
                            carolynhaywood

                            I think there were some formattting problems with the computer I was using at work.

                            Table jobinformation

                            Table Billabletime

                                 - Table Billabletimeselected is an occurance of table Billable time

                            Table Codes

                                 - Table CodeType is an occurance of table Codes

                                 - Table CodeType is an occurance of Table Codes

                                  - Table CodeCode is an occurance of Table Codes

                            Table occurance billabletimeselected has a field job id and a field billableid 

                            Table Job Information has a field job id and a global field billableIDselected.

                            The relationship between the two is jobID =JobID  AND billableid = billableIDselected

                             

                            So this statement by you is correct: 

                            Perhaps you have:

                            JobInformation::JobID = BillabletimeSelected::JobID

                            PLUS

                            Jobinformation::billableIdSelected = BillabletimeSelected::billableID

                            CODE OCCURANCE RELATIONSHIPS:

                            CodeType::type =CodeSection::Type

                            CodeSection::section=CodeCode::section

                             

                            I have 4 value lists based on the relationships of these tables.

                            1. Type = Codetype::type; all values

                            2  Section = CodeSection::section; values from Codetype

                            3  Code = CodeCode:code; values from CodeSection

                            4 Description = CodeCode::descripton; values from CodeSection

                             

                            I have two tabs with 2 portals on a layout based on table job information

                            tab 1 = billabletime portal

                            tab 2 = billabletimeselected portal

                            each record is connected by a button with the script:  "'set field jobinformation::billableidselected;billabletime::billableid"

                            Portal billabletimeselected has 4 fields based on the value lists above: Type, Section, Code, Description.

                            Each field as the appropriate value list assigned to it.

                            When a new record is created the field type works - a pop up menu appears with two values to choose from.

                            When you click on section, code, and description, it says "no values defined"

                            that is my problem.

                            • 11. Re: Please help! Portal/relationship question  new info
                              carolynhaywood

                              I have narrowed down the problem.

                              The conditional relationships between the three code tables do not work.

                              If I show all values instead of all values from x, I get the full drop down list of section, or code or description.

                              I don't understand why they don't work. 

                              Here is the relationship

                              CodeType::type = CodeSection::type

                              CodeSection::Section = CodeCode::Section

                              The value lists 

                              1.  type - CodeType::type ; all values

                              2.  section - CodeSection:section ; related values from CodeType

                              3.  code - CodeCode::Code ; related values from CodeSection

                              4  descritpon - CodeCode::descritpion ; related values from CodesSections.

                               

                              So something is wrong with the relationship between the tables.