Is it possible to generate a bibliography reference in Base?

[gherrarte]

I have a working small library database that I wish to automate as much as possible. I have a field that contains the reference of books, articles, book chapters, etc. I want to generate a summary field that will contain the bibliographical reference to each record based on the type of record. For example, if I have an Article, then I want Base to automatically retrieve Author, Title, Journal, Volume and Pages. I want all this information to be stored into one field. Is it possible to do this in Base?

I am using a Firebird embedded DB in LO Base 6.3.

The information is stored into different tables. “Article” is stored in the table “Type”. Authors is stored in a separate table, as all other relevant fields. All tables are related to each other via one to many relations. I have a Master table called Library.

(/upfiles/1574734788412801.png)

[Ratslinger]

Hello,

I have a working small library database

So this is an existing DB? What DB is it? HSQLDB or Firebird embedded or MySQL or something else? What specific version of LO are you using? You want to retrieve information but where is this information. Not clear as to what this means:

I want all this information to be stored into one field.

Is that another table? A field in some existing record?

Where is the “Article” coming from?

Sorry but these are just some of the questions that pop to the front with this question.

Please edit your question with further information as you description is very sketchy.

[gherrarte]

Thanks Ratslinger. I updated the question as you suggested.

[Ratslinger]

OK. On the surface this seems to be just a matter of an SQL statement(s); maybe a bit complex to get all the pieces together.

Are you familiar with SQL? Also you never answered as to what to do with this result. And it is appears you want to be able to take a specific “Article” (Type) and then request this generated result? Possibly from a form?

Also some of the tables may be confusing from the image provided. For example, there is an “Author” table and also an “Authors” table?

All seems possible.

[gherrarte]

Thank you. Yes. It makes lots of sense to use SQL. I will definitely explore this possibility. Thanks.

[Ratslinger]

Hello,

Will present something based upon the information already provided.

You can get all the information together by using SQL (a Query). Loosely based a sample on your request. Here is the result:

This actually starts in the middle with:

(Select "id_author", "Title", "id_journal", "vol no", "pages" from "LibraryDB", "Type" where "Type"."id_type" = "LibraryDB"."id_type")A

It selects the LibraryDB info based upon the Type (note that the A, B, C you see are aliases for the select statements).

Then the Author and journal information is joined to that:

Left Join (select "Author", "id_author" from "Author")B on A."id_author" = B."id_author"
Left Join (select "journal", "id_journal" from "journal")C on A."id_journal" = C."id_journal")

The final portion:

Select "Author" || ' --- ' || "Title" || ' --- ' || "journal" || ' --- ' || "vol no" || ' --- ' || "pages" AS "My RESULT" from

which is the top of the SQL takes all the information and concatenates that into one field.

Sample containing this ----- Bibliography.odb

I would like to recommend in the future you should be more consistent with your naming. You shouldn’t use mixed case or spaces in the names. Lower case with underscores is good.

[gherrarte]

Ok. I see. Thanks. I will try this solution. I will let you know.

[Ratslinger]

Also if you want to select a specific ‘Type’ (Article) change the main line (first in answer) to:

( SELECT "id_author", "Title", "id_journal", "vol no", "pages", "Type" FROM "LibraryDB", "Type" WHERE "Type"."id_type" = "LibraryDB"."id_type" and "Type" = :myType ) "A"

This will prompt to enter the type wanted.

[gherrarte]

It works. Thank you. I will undust my Sql notes.