1 2 3 Previous Next 30 Replies Latest reply on Mar 18, 2013 9:04 AM by HeidiStewart

    autocomplete value from record in table B if values entered into table A match?

    HeidiStewart

      Title

      autocomplete value from record in table B if values entered into table A match?

      Post

           I have a table with a list of students, I identify each student using a student ID, i have fields called "Status"; "Program" and "International" there are 16 possible combinations, each combination gives a different tuition amount. 

           I have another table with my tuition amounts broken down, i have linked the "Status"; "Program" and "International" fields in this table to the previous table with my students.

           How do i set up a field that when i enter a student and i select the status, program and international, that it will pull the tuition amount from the other table?? 

           also, i'm entering the tuition amounts per academic year, but my table of students doesn't indicate academic year, how do i make it choose the most recent academic year tuition?

        • 1. Re: autocomplete value from record in table B if values entered into table A match?
          philmodjunk

               This relationship will match a student record to a specific tuition amount record by the criteria that you specify:

               Student::AcYear = TuitionAmounts::AcYear AND
               Student::Status = TuitionAmonts::Status AND
               Student::Program = TuitionAmounts::Program AND
               Student::International = TuitionAmounts::International

               And then a TuitionAmount field in the Student data can use a looked up value field option to auto-enter a value from the TuitionAmounts table.

               All 8 match fields must contain data for this to work. AcYear can be a global field so that you select the academic year once for your session. It can also be an unstored calculation field that computes a value from the current date or froma value in a related table.

          • 2. Re: autocomplete value from record in table B if values entered into table A match?
            HeidiStewart

                 ok i created a global field in my student table for Academic Year and linked it to Academic Year in my tuition table.  the status, program and international fields are linked.

                 i created a Tuition amount field in my student table and put in the looked up value option, but the field is blank in all my records, almost like it can't find anything.  Not sure what i did wrong.

            • 3. Re: autocomplete value from record in table B if values entered into table A match?
              philmodjunk

                   Doesn't sound like you have done anything wrong.

                   As a test, create a new student record and select values in the 4 match fields. Does a tuition amount appear?

                   If so, you can put the cursor in one of the 4 match fields, show all records and select Relookup to update your existing records to copy over the current tuition amount values.

                   You may not want to use a looked up value setting that copies over a tuition amount like this. If you simply refer directly to the tuition amount field in the related table, such as by putting that field from the related table directly on your student layout, the value will appear and you will not need to use Relookup to copy over the values.

                   Here's the trade off: If you directly refer to the related table's amount field, any changes to that amount will automatically appear. If you use a looked up value setting, changes to the amount in the tutionAmount table will not automatically update the values shown in the Students table.

                   You'll have to decide which option works best for your use of this data. Often, invoicing/billing systems use a looked up value setting because they can't allow the values shown in invoices that have already been sent to the customer to change after the fact if a tuition amount is changed in the related table.

              • 4. Re: autocomplete value from record in table B if values entered into table A match?
                HeidiStewart

                     the tuition amounts only change once at the beginning of each new academic year.  i'm not worried about overwritting the previous value, this will happen anyways for remaining students when we switch over to the new academic year.

                     How do i directly refer to the tuition amount from the tuition table, the tuition table isn't entered in using student ID#'s, so i need the field to go look at Academic Year, Status, Program & International Stu in order to find the correct tution to use from the tuition table.

                     Thank you for your help! 

                • 5. Re: autocomplete value from record in table B if values entered into table A match?
                  philmodjunk

                       Go to your student layout.

                       Enter layout mode.

                       Use the field tool to add a new field object to your layout.

                       Select the TuitionAmount field from the related TuitionAmount table occurrence.

                       This adds the tutionamount field from the related table directly to your Student table.

                  • 6. Re: autocomplete value from record in table B if values entered into table A match?
                    HeidiStewart

                         done, but it's blank because the tuition amounts table doesn't specify which student ID is paying which tuition amount.  I need the field to do a search to compare the 4 fields and then pull the tuition. 

                         In excel i'd write a nested IF statement like IF(Student::Academic Year = Tuition::Academic, IF(Student::Program = Tuition:: Program, IF(Student::Status = Tuition::Status, IF(Student::International Student = Tuition:: International Student, Full Tuition Amount,"N/A"),"N/A"),"N/A",)"N/A") 

                    • 7. Re: autocomplete value from record in table B if values entered into table A match?
                      philmodjunk

                           It should not be blank and the student ID has no bearing on this issue.

                           Did you add the field from the TuitionAmount table to your student layout?

                      • 8. Re: autocomplete value from record in table B if values entered into table A match?
                        HeidiStewart

                             Yes i did, it's blank in all records.  I don't understand how the system would know which tuition amount from the Tuition table (there are 16) to pull from for each individual student.  Shouldn't i be creating some kind of calculation so the system knows to check the 4 fields?

                        • 9. Re: autocomplete value from record in table B if values entered into table A match?
                          philmodjunk

                               You have a relationship with 4 match fields:
                               Student::AcYear
                               Student::Status
                               Student::Program
                               Student::International

                               They must contain values that match the corresponding match fields in the related table:

                               TuitionAmounts::AcYear
                               TuitionAmonts::Status
                               TuitionAmounts::Program
                               TuitionAmounts::International

                               If they do, then a copy of TuitionAmounts::TuitionAmount, when placed on the Student layout, will display the Tuition amount for that combination of the 4 values in your match fields.

                               If you have your relationship correctly defined, the next thing to check is the 8 match fields. Each pair of fields should be the same data type. The values in each pair for a related pair of records must be exactly the same or the field will be empty.

                          • 10. Re: autocomplete value from record in table B if values entered into table A match?
                            HeidiStewart

                                 yes my Academic Year; Status; Program and International fields are linked.  3 of the 4 of those are drop down value lists, so the fields match exactly.  the academic year is typed in, but they both have 2012-2013.

                                 The Academic Year field in my students table is a global storage field, would that be the problem, because the field is still blank.

                            • 11. Re: autocomplete value from record in table B if values entered into table A match?
                              philmodjunk

                                   It should be a global field as otherwise you'd have to set this value each time you change to a different student record.

                                   Check the data types in the fields. The two academic year fields, for example, should both be of type text. Becareful of invisible characters that might keep the records from matching as well. 2012-2013 will not match to the value 2012 - 2013. (There are spaces before and after the - in the second instance.)

                              • 12. Re: autocomplete value from record in table B if values entered into table A match?
                                HeidiStewart

                                     ok, both academic year fields are text and i made sure they were the exact same.  the tuition field is still empty. 

                                • 13. Re: autocomplete value from record in table B if values entered into table A match?
                                  HeidiStewart

                                       ok i just tried something similar, i tried to add the first and last name fields from my Faculty of Science table and put them on a layout based on my Mandatory Courses table, they are blank.  if my StuID# is linked between the two tables shouldn't it be able to pull the first and last names in?

                                  • 14. Re: autocomplete value from record in table B if values entered into table A match?
                                    HeidiStewart

                                         ok sorry about the multiple posts, but i just found something else.  if i manually go into my Mandatory Courses layout and manually add in a student's ID and select the dept the first and last name fields populate.  but only if both StuID# and Dept are filled in.  Why would it need me to specifiy the Dept, each StuID# is unique, that field alone should be enough for the system to be able to pull the first and last name from the other table.... shouldnt it??

                                         How about we try something else.  if i could create a button that when i press it, a pop up box with all available courses in the matching dept show up, and i can pick one and it will assign that course to the student. ??

                                    1 2 3 Previous Next