Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1

    Unanswered: Creating Unlimited Colums and Rows

    hello Everyone... thanks for your replies to my posts earlier...

    im creating a new form i want help with something. on the form i have a table-like structure which is suppose to take data on, category, positions, and years of the positons (will attach.. a picture of how it looks like). i want the supposed details that will be entered to be unlimited. more like, if i enter the details in one row then another row will be automatically generated below to add the next details. ive seen it been done in my hospital's database system. seems like a common thing.

    to make matters clear... ive attached pics of what ive done and what i see in my hospital's database here. that of the hospital's has just two roles... but as you key in the drugs, more are added to create space for the next ones to add. making the column and rows "unlimited"

    ... Click image for larger version. 

Name:	IMG_7879.JPG 
Views:	34 
Size:	2.44 MB 
ID:	15994Click image for larger version. 

Name:	database.jpg 
Views:	32 
Size:	214.5 KB 
ID:	15995

    thanks

    Regards
    Evans

  2. #2
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    You need to create an additional form to store the data, and a many 2 many field.

    See attached example.

    Regards
    James.
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1
    Thanks James... ive seen your attachment... when you click on the add row button it kind of add a record.... actually its just an additional information for the same record that should be in the table not a record. ive attached a copy of my dbb file so you can check it up. what i want is more like a table that will keep details for one record on a differnt sheet.

    Thanks for replying
    Regards
    Evans

    Quote Originally Posted by Tubbritt View Post
    You need to create an additional form to store the data, and a many 2 many field.

    See attached example.

    Regards
    James.
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Hi Evans.

    Brilliant Database does not have Tables at all. In order to have a field in your form that can receive an unspecific amount of data as you want, you really don't have much choice. From what I can see you have to use a “Many 2 Many” field, and Many 2 Many fields can only display the contents of records from another folder.

    At best all you can do is simply improve on the idea by making the folder that contains the record invisible to users in order to fake the appearance of traditional tables inside brilliant database.

    Either way, the records must exist exactly as I demonstrated.

    As for hiding the folder, only the SDK (Ultimate) edition is able to hide folders in the compiled app from both Administrators and normal users. If your using a lower version of brilliant database, you will only be able to hide the folder from regular users.

    Regards
    James.
    Last edited by Tubbritt; 12-03-14 at 17:53.

  5. #5
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi Java7
    You do have a lot fields in 1 record (form) and wondering if it might be possible to break this up in to more related forms? I understand you want to emulate another program but is this going to force you in to a corner? With my limited understanding of "relational theory" each "bit" of imformation should have it's own record. Even things like customer phone no;s which are often included in customer record are more flexible stored in related records - ie you can add as many as you need with it's own description just as an example.

    Just some thoughts.
    David

  6. #6
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1
    @James... Thanks alot... I've been able to emulate what you did in the example you attached. Like the variable , queries and script stuff though I don't understand but I have the results. I think the issue will be hiding the folders which is a possibility too. Thanks alot. I really appreciate your help.

    Best Regards
    Evans

  7. #7
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1
    @David.. Your suggestion just added up to that of James...thanks too. I think I will need some theory from you kinda. How do I understand relational theories? At Least for the start lol

    Regards
    Evans

  8. #8
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1
    Good Day ... Pls I have another concern. I have been able to get the table as I want. My next issue is exporting the contents of the table to excel... Pls how do I do that.

    Regards
    Evans
    Quote Originally Posted by Tubbritt View Post
    Hi Evans.

    Brilliant Database does not have Tables at all. In order to have a field in your form that can receive an unspecific amount of data as you want, you really don't have much choice. From what I can see you have to use a “Many 2 Many” field, and Many 2 Many fields can only display the contents of records from another folder.

    At best all you can do is simply improve on the idea by making the folder that contains the record invisible to users in order to fake the appearance of traditional tables inside brilliant database.

    Either way, the records must exist exactly as I demonstrated.

    As for hiding the folder, only the SDK (Ultimate) edition is able to hide folders in the compiled app from both Administrators and normal users. If your using a lower version of brilliant database, you will only be able to hide the folder from regular users.

    Regards
    James.

  9. #9
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Quote Originally Posted by Java7 View Post
    @James... Thanks alot... I've been able to emulate what you did in the example you attached. Like the variable , queries and script stuff though I don't understand but I have the results. I think the issue will be hiding the folders which is a possibility too. Thanks alot. I really appreciate your help.

    Best Regards
    Evans
    When you press the button to add a new row to your table, a new record was being created in the records folder. So I wrote a query to find that record in the records folder. This one record will stand out from all the others because it contains no data. So I wrote a query to find any record in the Records folder that didn't have anything typed into it's Description field.

    A list of the record / or records the query found was then passed to the variable which allowed me to update the Many to Many field to display the records required on your table.

    Regards
    James

  10. #10
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Hello.

    To export to Excel first you must create an Excel file that contains the columns you want.

    For example, in column A1 you will type “Category”, in B1 you will type “Description” and so on.....

    You must save this file to your PC then as a excel 97/95 file, not the newer office 2007 format.

    In brilliant database you can then go to “File” followed by “Export/Send” followed by “Export to Access/Excel”.

    This will prompt you to create a template where you will be able to select the form type, and inside that form you will be able to select your Many 2 Many field.

    Where it says, “export to” “database” select your Excel file you saved a moment ago.

    At this point on the right will be displayed the columns you listed in your excel file, and you can see by clicking on the empty values that you can map brilliant database fields to those columns.

    Once your template has been created and is working, you can then assign it to a button or menu item.

    Regards
    James

  11. #11
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1
    Thanks James... I followed the procedures but in the window where you actually select which fields you exports to which column ... I only saw the name of the table created (after double clicking to display the fields to select) not the supposed colums (say description, year,...). So how do I get to get the columns in the table.

    Thanks ....
    Regards
    Evans
    Quote Originally Posted by Tubbritt View Post
    Hello.

    To export to Excel first you must create an Excel file that contains the columns you want.

    For example, in column A1 you will type “Category”, in B1 you will type “Description” and so on.....

    You must save this file to your PC then as a excel 97/95 file, not the newer office 2007 format.

    In brilliant database you can then go to “File” followed by “Export/Send” followed by “Export to Access/Excel”.

    This will prompt you to create a template where you will be able to select the form type, and inside that form you will be able to select your Many 2 Many field.

    Where it says, “export to” “database” select your Excel file you saved a moment ago.

    At this point on the right will be displayed the columns you listed in your excel file, and you can see by clicking on the empty values that you can map brilliant database fields to those columns.

    Once your template has been created and is working, you can then assign it to a button or menu item.

    Regards
    James

  12. #12
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Hmmm. Can you post your database and Excel file so I can take a look at it?

    I'll write this for you and then you can take a look at what I would have done. Might be easer this way than trying to problem solve.

    Regards
    James.

  13. #13
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1
    Good day. please i have attached the files requestd. I want to add my supposed last
    question on this issue. assuming the contents of the table is (amount paid, date paid ...)
    as in the table contains numbers how do you get a field below to table to get you the total amount
    (based on the values in your "unlimited" many-to-many table)

    Thanks for your help. Really appreciate it
    Regards
    Evans
    Attached Files Attached Files

  14. #14
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Hello.
    Are you using an old version of brilliant database or something? I can't open your file at all in v10 and I don't have an older copy of brilliant database installed on my PC.

    Regards
    James

  15. #15
    Join Date
    Oct 2014
    Posts
    88
    Provided Answers: 1
    Hi... Yes pls I'm using version 9.42 tried updating it but it's not working. Actually the previous ones I've created all I used same. Lets make it like... The file u sent to me if u can try applying my request in it to see if it's possible

    Regards
    Evans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •