Android Question database field with multiple values

[doogal]

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:

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

 

[fatman]

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

 

[doogal]

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

 

[Mahares]

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

 

[doogal]

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:

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]

Something like:

Dim crsr As Cursor = sql1.ExecQuery2("SELECT * FROM table WHERE DrugBrand LIKE ?", Array As String("%" & searchterm & "%"))

 

[doogal]

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]

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]

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.

 

[doogal]

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]

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!