11 Replies Latest reply on Sep 12, 2014 10:42 AM by jovi

    showing  data in portal by using sql commend

    jovi

      Title

      showing  data in portal by using sql commend

      Post

      Hi, 
       I just got a problem with showing data in portal.
      The portal tool just can be used to show only one tables' data, which is related to layouttable in filemaker
      so, every time, when designers login this page (pic), the data cannot be translated an ID(from Project ID) to designer page
      My database  like this  designer <---> user <---->Project, 
      My portal tool wants to be showed up Projects' data which is related to designer
      I am wondering that is that possible showing a data by using sql commend instead of useing setup wizard ?

      Thanks

       

      Untitled.png

        • 1. Re: showing  data in portal by using sql commend
          philmodjunk

          You cannot, but perhaps you can post a more detailed description of what isn't working for you.

          If you have these table occurrences:

          Designer-------user-----<project

          Then you can certainly put a portal to Project on a Designer layout and it will show data from Project in that portal, provided that there is a record in user that successfully links the current Designer record to records in project.

          • 2. Re: showing  data in portal by using sql commend
            jovi

            I have been trying this way which you just give me but it didnot work  
            My database below here
            Problem is Co_ Project data cannot be showed up on designer table layout
            is any problem in my database?

            • 3. Re: showing  data in portal by using sql commend
              philmodjunk

              The relationships shown can work to show data from CO_Projects on a layout that specifies Designer_Account as its "Show Records From" table occurrence in Layout Setup...

              But only if there is a record in User_Account where PK_User_ID matches to the value of DS_ID for the current record on your layout AND also matches to FK_User_ID of one or more records in CO_Projects.

              So I'd check field types, (all three should be the same type) and field values (They should be exactly the same unless one field stores a return separated list of values as seems unlikely) of all three fields. You might want to take the current value of DS_ID and use it to manually perform a find for records in the other two tables, using layouts that are based on those two tables as a way to check your data.

              Note, the fact that DS_ID and PK_USER_ID both appear to store unique values is a bit unusual here...

              • 4. Re: showing  data in portal by using sql commend
                jovi

                Thanks I will try it out 
                I am trying to design a project management  
                user table is for all user that include project manager, designer and Admin.
                I separate two pages to show their own project by users and designers .
                one of problem is that a project need to be setup two or more designers and also I want to  designers page show all of their projects
                that's why i create my database like this. 
                 

                 

                • 5. Re: showing  data in portal by using sql commend
                  philmodjunk

                  If you have more than one designer for a project and more than one project for a designer, you need a many to many relationship and your existing setup is not correct for that.

                  Start with these relationships:

                  Designers-----<Designer_Project>-----Projects

                  Designers::__pkDesignerID = Designer_Project::_fkDesignerID
                  Projects::__pkProjectID = Designer_Project::_fkProjectID

                  You can place a portal to Designer_Project on the Designers layout to list and select  Project records for each given Designer record. Fields from Projects can be included in the Portal to show additional info about each selected Project record and the _fkProjectID field can be set up with a value list for selecting Projects records by their ID field.

                  • 6. Re: showing  data in portal by using sql commend
                    jovi

                    thank you so much
                    You delicate where i mass up  
                    that's why  I felt very weird when I was trying to catch an data from designer  but it didnt work at all.


                     

                    • 7. Re: showing  data in portal by using sql commend
                      jovi

                      Hi, PhilModJunk

                      I have followed your suggestion to change my database . It works very well.
                      What if I have two or more projects in my database like this pic
                      Do you have any suggestions to me cos I cannot do like this scenario

                      Designers-----<Designer_Project>-----Co_Project
                               |
                               |-----------<Designer_Project2>-----AD_Project
                      I want to collect all designers' project and show all on designer page
                      but when I was trying to do this scenario, the database forced to add new table of designer like this
                      Designers-----<Designer_Project>-----Co_Project
                               
                       Desingers2---------<Designer_Project2>-----AD_Project
                      Problem is here the layouttable (Designer )just can show  information which is relative to Co_Project on designer page
                      but i want to get both of them on designers' page

                       

                       

                      • 8. Re: showing  data in portal by using sql commend
                        philmodjunk

                        To answer the immediate question, FileMaker cannot allow a "cycle" to appear in a chain of relationships, so when you try to define a relationship that creates such a result, it pops up an error message and offers to make a new table occurrence (Instance) of one of the two tables. But you do not have to agree with FileMaker's guess as to which table occurrence to duplicate. In this case, you can click the other table occurrence involved, click the duplicate button (two green plus signs) to make a duplicate occurrence of that table and then drag from it to create your relationship.

                        But you might want to consider whether you should have separate tables for each kind of project in the first place. It may be a better design to put the records for both types of projects into the same table and use a field in the table to identify which are AD and which CO projects. There are quite a few ways that you can use in your layout design to control whether you are working with one type of project or the other.

                        Caulkins Consulting, Home of Adventures In FileMaking

                        • 9. Re: showing  data in portal by using sql commend
                          jovi

                          Thanks 
                          you mention that "
                          may be a better design to put the records for both types of projects into the same table and use a field in the table to identify which are AD and which CO projects. "
                          So,For example, if AD  has ID, Name, Category... and CO has ID, Name, Status  ....
                           we still can put it together, 
                          even though AD and CO dont have the same field, right?
                          In the beginner of my design, I thought each of the project had different field, so I setup each of the project was individual
                          What if i have already setup this way,which i have separate table of project,  does that cause any problems or any efficiency of database? 
                            

                            

                          • 10. Re: showing  data in portal by using sql commend
                            philmodjunk

                            we still can put it together, even though AD and CO dont have the same field, right?

                            well, they do have some fields in common, ID and Name. One option is to include both category and status, leaving them blank but don't use them. But keep in mind that I said this might be a better option.

                            What if i have already setup this way,which i have separate table of project,  does that cause any problems or any efficiency of database? 

                            There's no simple answer to that question as it depends on the total design of your database and how you use it. The advantage to a common table here is that certain reports where you might want to include data from both of these tables and also the Designer table become a lot easier to create as you can sort the records by type to get one part of your report  based on CO and part on AD, with both including info from Designer. But you might not even need that report.

                            And there's even a "middle ground" approach where you set up a common "projects" table with all the fields common to all types of projects, but then link in separate "project detail" tables that hold the fields that are unique to one type or the other...

                            • 11. Re: showing  data in portal by using sql commend
                              jovi

                              Gotcha 

                              Thanks  for your common
                              It's very useful for me