0 Replies Latest reply on Nov 29, 2012 4:18 AM by ClaudiuNemes

    Dynamically create records in a table

    ClaudiuNemes

      Title

      Dynamically create records in a table

      Post

           Dynamically create records in a table.

           Hi there,
           I need assistance regarding dynamically creating records in a table.
           I have the following situation.

           ***
           I have a database called Projects with the following tables:

      1. Projects
           Prj_ID:
           Prj_Name:
      Prj_DurationInYears:  3 (3 years)
           Prj_StartingYear: 2012
           Student_NoOfDisbursementsPerYear: 4
           Mentor_NoOfDisbursementsPerYear: 12

           Etc.

      2. Students(a student could be involved in many projects; a student is tutored by one mentor)
           S_ID:
           Prj_ID:
           M_ID
           Prj_Name: (dropdown values list from Projects::Prj_Name)
           FirstName:
           Initials:
           FamilyName:
           Etc.

      3. Students_Evaluation
           SE_ID:
           S_ID:
           FirstName: (calculation: Students::FirstName)
           Initials: (calculation: Students::Initials)
           FamilyName: (calculation: Students::FamilyName)

      /* from here down I want, If is possible to create dynamically as many records as they are set for the number of years in the Projects Table (for this project only let’s say 3, starting from 2012; so a table like the one bellow) */

      … 2012   GPA_SEM_I   GPA_SEM_II   GPA  …
           … 2013   GPA_SEM_I   GPA_SEM_II   GPA  …
           … 2014   GPA_SEM_I   GPA_SEM_II   GPA  …


           Year:
           GPA_SEM_I:
           GPA_SEM_II:
           GPA:
           Total_Absences_SEM_I:
           Unjustified_Absences_SEM_I:
           Justified_Absences_SEM_I:
           Total_Absences_SEM_II:
           Unjustified_Absences_SEM_II:
           Justified_Absences_SEM_II:
           Total_Absences:
           Unjustified_Absences:
           Justified_Absences:
           /* end */

           Etc.

      4.Students_Disbursements
           SD_ID:
           S_ID:
           FirstName: (calculation: Students::FirstName)
           Initials: (calculation: Students::Initials)
           FamilyName: (calculation: Students::FamilyName)

      /* from here down I want, If is possible to create dynamically as many records as they are set for the number of years in the Projects Table and also as many DisbursementsPerYear are set in the same table for students (for this project only let’s say 3, starting from 2012, with 4 disbursements per year; so a table like the one bellow) */

      … 2012   D1   D2   D3   D4   Total …
           … 2013   D1   D2   D3   D4   Total …
           … 2014   D1   D2   D3   D4   Total …


           Year:
           Disbursements: “D”+i
           Total:
           /* end*/

           Etc.

      5. Mentors(a mentor could be involved in many projects; a mentor could tutor at least 5 students but no more that 10)
           M_ID:
           Prj_ID:
           S_ID
           Prj_Name: (dropdown values list from Projects::Prj_Name)
           FirstName:
           Initials:
           FamilyName:
           Etc.

      6. Mentors_Disbursements
           MD_ID:
           M_ID:
           FirstName: (calculation: Mentors::FirstName)
           Initials: (calculation: Mentors::Initials)
           FamilyName: (calculation: Mentors::FamilyName)

      /* from here down I want, If is possible to create dynamically as many records as they are set for the number of years in the Projects Table and also as many DisbursementsPerYear are set in the same table for mentors (for this project only let’s say 3 years, starting from 2012, with 12 disbursements per year; so a table like the one bellow) */

      … 2012   D1   D2   D3   D4   D5   D6   D7   D8   D9   D10   D11   D12   Total …
           … 2013   D1   D2   D3   D4   D5   D6   D7   D8   D9   D10   D11   D12   Total …
           … 2014   D1   D2   D3   D4   D5   D6   D7   D8   D9   D10   D11   D12   Total …


           Year:
           Disbursements: “D”+i
           Total:
           /* end*/

           Etc.
           ***

           Because the beneficiary (a nonprofit organization) have to export data quite often in order to report in some standard formats, required by the evaluation entities, I want to reduce the number of database fields by using the table relationships and portals. They also need the export in order to feed other databases, so from this point of view creating each specific field will be a good solution, while I could export the whole database into a single file. But again this will require the creation of hundreds of fields (if not more).

           Using repeating fields could solve the problem but it’s quite hard to export repeating fields.

           Any suggestion will be appreciated. Maybe the structure is wrong or incomplete? Maybe I have to use as many table occurrences as many years I have in each project? Maybe is better to split the database into project files?

           Many thanks for your time,
           Yours,
           Claudiu Nemes