1 2 Previous Next 16 Replies Latest reply on Mar 30, 2016 4:13 PM by carrillas

    Value List based on the same table with 1 criteria.

    carrillas

      I am very new to FileMaker and I have having trouble in creating a value list that is based off of the same table using one criteria. Here is the story, I manager a program where applicants go through a jury section process; some are selected and others are not. I have a table titled Applicant Information with a field titled Selected with a value list of Yes or No. I am creating a form layout and I want to add a value list with only the applicants that were selected. How do I create such a value list?

        • 1. Re: Value List based on the same table with 1 criteria.
          dsvail

          do you mean you want to only show those applicants that have been selected... a found set where the field 'Selected' ="Yes" ?

          • 2. Re: Value List based on the same table with 1 criteria.
            coherentkris

            You need to look up information on conditional/related, table based, value lists.

            Creating conditional Value Lists | FileMaker

            If you have a constant = "Yes" as a field on the left side of a relationship and set your value list to get the values across the relationship you will get only those values on the right where field "Yes"

            • 3. Re: Value List based on the same table with 1 criteria.
              carrillas

              Thank you for your replies. I have created a calculation filed labeled SelectedApplicants and entered "Yes" in the specified calculation dialog box. I have created a second ApplicantionInfo TO and created a self joined relationship. In the Edit Relationship dialog box the new TO is on the left side. I added a second criteria showing:

                   And SelectedApplicants = Selected

              Is this correct?

              • 4. Re: Value List based on the same table with 1 criteria.
                dtcgnet

                Post a screen shot of your relationship. That'll help us.

                 

                As for your calculated field, it won't give you what you want. All applicants will have "Yes" as the result of the calculation. You should not use a calculation. For this, you should have a text field. Later, we'll discuss how you're going to set that value to Yes, but we need more info first.

                 

                I don't think you want a value list. If you do, I think I'd probably try to talk you out of it. A value list is something like "Yes" or "No", and the user can select one of those values. I can see where you might want a list of the names of the people who were selected, but that's different.

                 

                So...how are you going to mark each person as selected or not selected? Let's answer that first. Find the name, then click a "Yes" radio button, possibly?

                • 5. Re: Value List based on the same table with 1 criteria.
                  carrillas

                  This FM solution will be used to manage a program that my department oversees for the local government agency that I work for. The intent of the Selected Applicant value list is that it will be used in a form layout designed to track code compliance by each successful applicant. Please see the screen shots below:

                   

                  Table:

                  Screen Shot 2016-03-29 at 4.06.44 PM.png

                  Relationship Graph:

                  Screen Shot 2016-03-29 at 4.07.13 PM.png

                  Edit Relationship Dialog:

                  Screen Shot 2016-03-29 at 4.07.23 PM.png

                  Inspection form Layout. It is the ApplicantID that I would like to use at the popup showing only the selected applicants:

                  Screen Shot 2016-03-29 at 4.08.49 PM.png

                   

                  Any additional guidance you can provide is extremely helpful and appreciated.

                  • 6. Re: Value List based on the same table with 1 criteria.
                    dtcgnet

                    Applicant...is someone applying for a job?

                    Is this something regarding a jury selection process?

                     

                    Devil's in the details, so it's good to ask the questions.

                     

                    Is this something where people are asked to come to jury duty, and you do something when a particular person is selected for a particular jury? Like, there's a jury pool of 100 people, and you somehow enter all of that information. Then as the jury is selected, you indicate that some people were selected and some were not? Sometimes, a person is asked to be ready for jury duty for a month, and there might be numerous trials for which the person could be selected or not selected for jury duty.

                    • 7. Re: Value List based on the same table with 1 criteria.
                      carrillas

                      Applicants are local artists/artisans applying to participate in a Artist/Artisan program. Applicants are juried by a panel other artists/artisan not involved in this program. The jury panel uses a point system and a set of specific criteria identified by city code. There are limited number of spaces available; the highest ranking applicants are awarded licenses for a 5 year term. The city code has specific requirements for each successful vendors to comply with. For many years spaces inspection were recorded on paper.

                       

                      After a significant amount of time researching mobile solutions it seems as FileMaker Pro along with FileMaker Go (with an iPad Air 2) seems to be the best solution to automate this daily inspection process.

                       

                      Thank you again for you guidance and assistance.

                      • 8. Re: Value List based on the same table with 1 criteria.
                        dtcgnet

                        Perfect and very helpful.

                         

                        I see a Score Sheet table, linked to the Applicant table via Applicant ID. Let's say I applied last year and got a score of 50. This year I applied and got a score of 100. Be careful because if you kept a total of my score, it would be 150, and I'd be ahead of an applicant who applied once and got a score of 149.

                         

                        Describe the scoring process. Is there a start/finish? So submissions for 2016 are due by June 1st, and on June 15th, scores are tallied and spaces allotted based on this year's scores?

                         

                        Is the data regarding which applicants have licenses that are still current in the database? I won three years ago, so I still have two years remaining...Is that in the database? Did I have to reapply in any way?

                        • 9. Re: Value List based on the same table with 1 criteria.
                          carrillas

                          The license term is for a period of five years (January of year 1 - December of year 5; there's only one jury selection process that is held six months before the new license term begins. My intent is, if and when I perfect this FM solution, I will create a copy (template) and use one solution for each license term. Is this logical?

                           

                          The application submittal process is open for 30 days. All applicants are required to submit an application packet based off of a set of required submittals. Each applicant is granted a set time to make their presentation to the jury panel; once completed the jury panel complete their individual score sheet. The score sheets are collected immediately and the results were previously entered into an excel spreadsheet (I will be using FM for this moving forward). Scores are tallied and sorting in order of rank. The highest ranking applicants are then awarded a license.

                           

                          All applicant information remains in the database whether they were selected or not; however there is the need to identify which applicants were selected as there are several compliance requirement that need to be met and track throughout the license term.

                           

                          There are times in which a selected applicant decided they can no longer participate in this program and relinquishes their license. In these cases the license is then awarded to the next highest ranking applicant.


                          Once the license term is completed each vendor is required to go through the jury process again.

                          • 10. Re: Value List based on the same table with 1 criteria.
                            carrillas

                            In the applicant table there is a field titled Selected. In the applicant layout I used a "Yes" "No" radid button value list.

                            • 11. Re: Value List based on the same table with 1 criteria.
                              dsvail

                              almost... In the Edit Relationship between the two table switch the two fields ... the new calc.field (could also use a global field set to Yes) should be on Applicants and the actual value of Selected (Yes/No) should be on the on the Applicants_SELECTED TO.  Attached screen shots to clarify.

                               

                              Last screen shot show the value list created using the relationship you created above.

                              • 12. Re: Value List based on the same table with 1 criteria.
                                dsvail

                                Carrillas,  here is a sample file showing how to create value list you wanted. it corresponds to my last post.  I created two different ways to create that value list.  Highlighted in the relationship graph is the way you are trying to do it.... works.  the second way I utilized the selector/connector concept  using a table with just global fields (one record)  in-between the self -join....seems to be easier to understand when you create your value list.   Attached the value list config using that part of the relationship graph as well.

                                • 13. Re: Value List based on the same table with 1 criteria.
                                  carrillas

                                  dsvail, thank you for your guidance. In your last post I am having trouble finding the file you referenc. Please re-attach. Thanks.

                                  • 14. Re: Value List based on the same table with 1 criteria.
                                    dsvail

                                    look at other post .... zip file called Applicants.fmp12.zip 70.1k

                                    1 2 Previous Next