D doogal Member Licensed User Longtime User Apr 20, 2014 #1 I have an app that I can search medication names generic and brand. I now want to take this app a step further but, with the database(csv) I have I can't seem to figure out how to implement this idea. I want to not only search generic and brand names I want to know what the medication is used for and dosage info. The csv file is layed out like this: Code: Copy DrugGeneric DrugID DrugBrand DrugCategory DrugDesription DrugDosage DrugURL value1 001 multiple value1 desc1 dosage1 www.url1.com value2 002 multiple2;multiple3 value2 desc2 dosage2 www.url2.com value3 003 value3 desc3 dosage3 www.url3.com As you can see the DrugBrand field can have 1, multiple meaning a bunch or none and they are separated by semicolons. The other fields I believe I have under control. I just can't search the DrugBrand field. Is there a way of searching this field(DrugBrand) or does anyone know of a Drug Database that offers at least these 3 fields: Generic, Brand, and (Description or Category). If it helps I can upload my current project. Thanks to anyone who helps. Douglas Last edited: Apr 20, 2014
I have an app that I can search medication names generic and brand. I now want to take this app a step further but, with the database(csv) I have I can't seem to figure out how to implement this idea. I want to not only search generic and brand names I want to know what the medication is used for and dosage info. The csv file is layed out like this: Code: Copy DrugGeneric DrugID DrugBrand DrugCategory DrugDesription DrugDosage DrugURL value1 001 multiple value1 desc1 dosage1 www.url1.com value2 002 multiple2;multiple3 value2 desc2 dosage2 www.url2.com value3 003 value3 desc3 dosage3 www.url3.com As you can see the DrugBrand field can have 1, multiple meaning a bunch or none and they are separated by semicolons. The other fields I believe I have under control. I just can't search the DrugBrand field. Is there a way of searching this field(DrugBrand) or does anyone know of a Drug Database that offers at least these 3 fields: Generic, Brand, and (Description or Category). If it helps I can upload my current project. Thanks to anyone who helps. Douglas
F fatman Active Member Licensed User Longtime User Apr 20, 2014 #2 Hi doogal, it´s a matter of design. I´d use a sqlite-database instead of csv- thus giving you much more possibilities of seleceting/sorting stored informations. You may start here: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/ Fatman Upvote 0
Hi doogal, it´s a matter of design. I´d use a sqlite-database instead of csv- thus giving you much more possibilities of seleceting/sorting stored informations. You may start here: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/ Fatman
D doogal Member Licensed User Longtime User Apr 20, 2014 #3 Thank you for your reply Fatman. Maybe I wasn't real clear with what I needed. I have a csv file and I can convert it to sql database with no problems. What I am having trouble with is displaying and searching the DrugBrand field since it has multiple fields/entries. My current project doesn't have this problem, but it also doesn't have the extra information I want in this update. Here is a screenshot of my current project as an example. As you can see I can search this just fine. I would like to do the same with the new database, but just unsure how to deal with the DrugBrand field. Thank you. Douglas Upvote 0
Thank you for your reply Fatman. Maybe I wasn't real clear with what I needed. I have a csv file and I can convert it to sql database with no problems. What I am having trouble with is displaying and searching the DrugBrand field since it has multiple fields/entries. My current project doesn't have this problem, but it also doesn't have the extra information I want in this update. Here is a screenshot of my current project as an example. As you can see I can search this just fine. I would like to do the same with the new database, but just unsure how to deal with the DrugBrand field. Thank you. Douglas
M Mahares Expert Licensed User Longtime User Apr 20, 2014 #4 Your application is best suited using the SearchView class. You can continue to use your csv file to search through or preferably convert it to a SQLite database. See link below: http://www.b4x.com/android/forum/th...native-to-autocompleteedittext.19379/#content Upvote 0
Your application is best suited using the SearchView class. You can continue to use your csv file to search through or preferably convert it to a SQLite database. See link below: http://www.b4x.com/android/forum/th...native-to-autocompleteedittext.19379/#content
D doogal Member Licensed User Longtime User Apr 24, 2014 #5 Sorry for the long delay. I'm so busy, I do this as a hobby, and don't get much time. Anyway, I tried the SearchView class and its not going to work for what I need. I still need to break up the DrugBrand field values first. I have converted the csv file into a sql db file as I always do so sorry for the confusion in that. Everyone still calls it a csv file, its not. The layout however, is still the same: Code: Copy DrugGeneric DrugID DrugBrand DrugCategory DrugDesription DrugDosage DrugURL value1 001 multiple value1 desc1 dosage1 www.url1.com value2 002 multiple2;multiple3 value2 desc2 dosage2 www.url2.com value3 003 value3 desc3 dosage3 www.url3.com I want to search either the DrugBrand or DrugGeneric fields. DrugGeneric field only has one entry and I have no problem with that one, its the DrugBrand that has one, multiple or even none at all that's giving me a fit. How do I break up the DrugBrand field in order to display it properly? I have tried selecting semicolon when converting the csv file but that messed up so I left that option out. Thanks again, Douglas Upvote 0
Sorry for the long delay. I'm so busy, I do this as a hobby, and don't get much time. Anyway, I tried the SearchView class and its not going to work for what I need. I still need to break up the DrugBrand field values first. I have converted the csv file into a sql db file as I always do so sorry for the confusion in that. Everyone still calls it a csv file, its not. The layout however, is still the same: Code: Copy DrugGeneric DrugID DrugBrand DrugCategory DrugDesription DrugDosage DrugURL value1 001 multiple value1 desc1 dosage1 www.url1.com value2 002 multiple2;multiple3 value2 desc2 dosage2 www.url2.com value3 003 value3 desc3 dosage3 www.url3.com I want to search either the DrugBrand or DrugGeneric fields. DrugGeneric field only has one entry and I have no problem with that one, its the DrugBrand that has one, multiple or even none at all that's giving me a fit. How do I break up the DrugBrand field in order to display it properly? I have tried selecting semicolon when converting the csv file but that messed up so I left that option out. Thanks again, Douglas
Erel B4X founder Staff member Licensed User Longtime User Apr 24, 2014 #6 Something like: Code: Copy Dim crsr As Cursor = sql1.ExecQuery2("SELECT * FROM table WHERE DrugBrand LIKE ?", Array As String("%" & searchterm & "%")) Upvote 0
Something like: Code: Copy Dim crsr As Cursor = sql1.ExecQuery2("SELECT * FROM table WHERE DrugBrand LIKE ?", Array As String("%" & searchterm & "%"))
D doogal Member Licensed User Longtime User Apr 24, 2014 #7 Search works, but I don't think I can display this like I need the way it is. I'm going to have to work on either finding a new database or work on the one I have. Thanks for all your help. Upvote 0
Search works, but I don't think I can display this like I need the way it is. I'm going to have to work on either finding a new database or work on the one I have. Thanks for all your help.
incendio Well-Known Member Licensed User Longtime User Apr 29, 2014 #8 I think you should redesign your table. Create 2 tables, Drug and Brand. Drug table, contains all field as sample except for DrugBrand. Brand table, contains drugid, brandname. Upvote 0
I think you should redesign your table. Create 2 tables, Drug and Brand. Drug table, contains all field as sample except for DrugBrand. Brand table, contains drugid, brandname.
sorheim Member Licensed User Longtime User Apr 29, 2014 #9 I am taking a database class right now and having multiple pieces of information in a single field is bad. Incendio is absolutely correct in advising to create more tables. This is one of the steps in database normalization. Upvote 0
I am taking a database class right now and having multiple pieces of information in a single field is bad. Incendio is absolutely correct in advising to create more tables. This is one of the steps in database normalization.
D doogal Member Licensed User Longtime User Apr 29, 2014 #10 I understand having multiple pieces of information in a single field is bad. This is what is frustrating, but I didn't create this database. I downloaded it from here and told the site what info I wanted in the database; however the first column cannot be changed it has to be the Generic Drug and that is what is messing the whole thing up. I would like it to be Brand name. I don't know how to fix this to make it work in my app. separating by semicolons didn't work. Upvote 0
I understand having multiple pieces of information in a single field is bad. This is what is frustrating, but I didn't create this database. I downloaded it from here and told the site what info I wanted in the database; however the first column cannot be changed it has to be the Generic Drug and that is what is messing the whole thing up. I would like it to be Brand name. I don't know how to fix this to make it work in my app. separating by semicolons didn't work.
sorheim Member Licensed User Longtime User Apr 30, 2014 #11 After looking at the site, I better understand your frustration. They offer A LOT of data but the tools for getting it are cumbersome. I even used their 4.0 Beta search tool and it looks no better than the link you sent. I would really be interested in transforming the information to a proper RDBMS but unfortunately my school commitments do not leave me with much time at the moment. I suggest contacting this guy to see if he can offer a solution to get the data in a sqlite friendy format. http://www.wishartlab.com/members/craig-knox Maybe better to use this. http://feedback.wishartlab.com/?site=drugbank Good luck! Last edited: Apr 30, 2014 Upvote 0
After looking at the site, I better understand your frustration. They offer A LOT of data but the tools for getting it are cumbersome. I even used their 4.0 Beta search tool and it looks no better than the link you sent. I would really be interested in transforming the information to a proper RDBMS but unfortunately my school commitments do not leave me with much time at the moment. I suggest contacting this guy to see if he can offer a solution to get the data in a sqlite friendy format. http://www.wishartlab.com/members/craig-knox Maybe better to use this. http://feedback.wishartlab.com/?site=drugbank Good luck!
