Page 1 of 1

[SOLVED] List box displays all relationships for all records

Posted: Sun May 26, 2019 7:35 am
by Doranwen
I'm not even sure if I'm describing my issue right (the subject line was hard to come up with), but I'll give it a shot. (Fwiw, I'm using LibreOffice 5.1.6.2 on Mint.) I've tried to search for this, but no combination of keywords I try pulls up anything like my situation, so I figured I'd better just describe it and see if someone can recognize what I'm trying to do - and where I'm going wrong with it.

I'm creating a small database for the stories (fanfiction, to be precise - fanfic for short) that I have saved on my computer, because I have difficulty remembering which one I want to re-read for one purpose or another, so I started simple and researched a ton (tutorials, Youtube videos, dozens and dozens of searches about this and that) till I had a working mini-database with five tables, a form, a couple queries, etc.

I have a table for the fics (with FanficID as the primary key) and attributes that are not multiple value but pertain to that fic alone.
I have a table for the authors (with AuthorID as the primary key) - and a mapping table that uses FanficID and AuthorID as compound primary key (because a story can be co-written by multiple authors). All good with that - though I didn't actually test putting a second author in a single fic, it seemed to allow me to do that.
I was able to create a list box to choose the author or authors for each story, and a combo box that let me add new authors (and see them in the list box to choose from with a refresh). The second author in the system was added using the combo box.
(I also set up checkboxes, radio buttons, and text fields in the form and had no trouble with that - worked perfectly.)

I did the same with fandoms, which added two more tables, one with FandomID as a primary key, and another mapping table with FanficID and FandomID as compound primary key (because there are such things as crossovers wherein a story belongs in multiple fandoms at once). That seemed to work successfully as well, though I also didn't enter any crossovers to test with.

Then I tried something very complicated - characters. Characters are unique to their fandom - and a specific fanfic can have anywhere from 0 to many characters assigned to it. I tried following an example online which seemed to suggest creating a table for the characters which had CharID as primary key, FandomID as foreign key, and Char as regular field. I had originally wondered if I shouldn't have just a separate table to map that relationship - but since I'm treating characters as unique to their fandom (in other words, the Clark Kent from Smallville is not the same Clark Kent as in Lois & Clark: The New Adventures of Superman), it's only a 1-to-many relationship (one fandom, many chars) and not a many-to-many. (It may be that I set up the tables incorrectly for this, and if so, I apologize in advance for posting this question in the wrong subforum.) However, since a fanfic can use many characters and a character can appear in many fanfics, I set up a mapping table with FanficID and CharID as a compound primary key to handle the many-to-many relationship.

I'd found upon adding the fandoms that adding on new tables with records already in the database meant that I needed to populate those new tables manually with data or the form would throw up errors about setting null values in not-null fields (even though I made sure the Char field did NOT require data entry). So I went into the Chars table and added four characters (IDs 1-4 using autovalue), assigning them all FandomID #1 (since I'm testing initially just with one fandom - though a second was entered into the system via the combo box on the form to test that I could do it). Then I went into the mapping table and tried to enter all the mapping that would result. The first fanfic had three of the four characters in it, so I set that, the second had two of the three, the third had just one, the fourth had only the fourth character.

Then I went to the form and created a list box the same way I had with Author and Fandom, and saved and opened the form... to see seven characters listed for the first story. Every character that I had mapped to a fandom appeared in the box for the first story, every time they were mapped. One character that was in all three first stories appeared all three times he was mapped. So I definitely did something wrong and I'm not sure what it is - the fact that the Chars table had the FandomID foreign key mapped? (Would it have worked better to have a mapping table with FandomID and CharID even if one character can't appear in more than one fandom?) What I want to have happen is for only those characters to appear that I assigned to that fanfic - and once only! The dropdown for a new character option is fine, but I need to also be able to remove one from the story if it's wrongly entered, and I have no clue how to do that either - the list box didn't seem to offer any way to delete the record or choose blank or anything. It seemed that every entry in the mapping table appeared as a dropdown for each fanfic. I'd be OK with having a "no characters" option - but there still needs to be a way to undo a character if I chose three for a fanfic and decided later that one of them didn't really pertain to the fanfic, for instance. I manually (from the tables) deleted all but one entry for each fanfic so it would be as if each fanfic had a single (different) character - and yet all four characters still appeared for every single fanfic.

I also realized I was really in trouble when it came to adding a new character - because I had to be able to assign it a fandom at the same time (my attempt to do so had the issue of displaying only the fandom ID number rather than the fandom name - though it did seem to work to add a new character that way). However, that may be an entirely separate (and unrelated) question, so I can post a topic on that later once I've sorted out the duplicate character display issue.

I'll be using the solution for this with relationships (which will be handled the same way as characters are), and I may want to do something similar to this with authors as well - most authors write for one fandom, though a few are cross-fandom, and the sheer number of different authors for any given fandom means that I will end up with far too many to choose from in a dropdown by the time I'm done. A way to map authors to the fandom they write in (and edit how many fandoms a specific author writes in) would be useful in later letting me limit the authors in the listbox to whatever fandom or fandoms I choose. (The filtering there is yet another issue that I plan on tackling when I've figured out this one - but my solution to this one may inform what I do with that.)

I've attached the database as it is currently for your perusal - the current version of the form I'm working on is the latest (Fanfics06). As I got a feature working properly, I copied the form and renamed it to do testing of a new feature, so I wouldn't inadvertently regress by messing up old work. (When I get it finished to usability, I'll delete the old versions of forms, queries, etc.)

Thank you in advance for any advice you have.

EDIT: I created a copy of the database and tried setting up the tables so Chars and Fandoms and Fanfics had a three-way relationship with mapping tables between all three (instead of FandomID being a foreign key in the Chars table). While I was able to get creating characters correct using a combo box - and even assigning them to the correct fandom using two list boxes in a single table control - I'm still having difficulty with the Fanfic_Char mapping table list box, wherein I would be assigning the characters to each fanfic. It still displays every mapping relationship set up in the mapping table, rather than the ones pertaining to that fanfic alone. (And if I try adding a character to the list, it complains about adding null values to a non-null field - the FanficID one.) I'm not really sure what's going on here.

The one thing I did discover - if I try to add another character (or another fandom, or another author), it complains about null values being inserted in non-null fields. So apparently that wasn't working, and I didn't realize it because I didn't test crossovers and co-authored fanfics. No clue how to fix that issue either…

Removed old versions of database as they are not relevant.

Re: List box displays all relationships for all records

Posted: Mon May 27, 2019 2:26 am
by UnklDonald418
I made some modifications to the form document Fanfics06 in Fanfic.obd to address the problems with characters. See if that resolves some of your issues. You had 5 SubForms that were not linked to anything, I linked 2 of them so there are 3 more that need to be addressed.
Fanfic_Mod02.odb
(93.24 KiB) Downloaded 263 times
I didn't do anything with Fanfic2.obd I'm not not sure you are headed in the right direction there.
 Edit: I just I had a AhHa moment. I think some of my modifications were wrong. EditChars should be on the same level as MainForm. Then you can Add/Edit Char records. I'm still trying to understand some of your constraints, particularly with the CharListBox SubForm. 

Re: List box displays all relationships for all records

Posted: Tue May 28, 2019 3:25 am
by UnklDonald418
Another attempt for you to try. There were enough changes that I added a form document Fanfics07.
Fanfic_Mod02(3).odb
(93.24 KiB) Downloaded 260 times
The "errors about setting null values in not-null fields" is caused by not having a column for FanficID in the table control on the SubForm FandomListBox. For a table control to successfully modify a row of data the primary key (in this case both parts of the compound primary key) must have a column on the the table. It can be hidden from the user, but without it Base doesn't know which record to tell the database engine to modify.
There is also a Push Button that initiates a Refresh Form action on the CharListBox. That may be needed to update the list of characters whenever there is a change in FandomListBox or EditChars

Re: List box displays all relationships for all records

Posted: Tue May 28, 2019 9:03 am
by Doranwen
Thank you for the help! I had an extremely busy day yesterday and today and didn't have a chance to look at it till now, so I'm looking at your second one right now. I'd thought it made sense to just mark the FandomID in the Chars table, but wasn't figuring out how to make that work.

Linking subforms! Of course! I knew there was a way I had to be linking the FanficID to the table but wasn't sure how to do that - and subforms automatically would look to the main form for that value, then? I can see how that would make all the difference. And that will be necessary as well when I add the next two tables (which I'm not going to do till I work out all the issues with this) - Ships (aka relationships), which will depend on the fandom, though I'm not going to try to link ships with characters, instead entering them separately and treating ships the same way I treat the characters.

I did find and use the refresh button for the main form before, but that would've cleared anything entered in the other boxes - the push button is very helpful! I'm now seeing other uses for it, looking at the actions list. You'll see that in my edit of the 7th version of the form (I ended up editing it before I remembered to copy, but I think I didn't change anything of your functionality, just added buttons and adjusted some of the layout).

Aha! So it looks like for the filtering, the trick is linking *both* the FanficID and FandomID fields in the Link Master/Slave Fields boxes… That makes so much more sense. Enclose the fields in question in separate pairs of quotes and a semi-colon to separate the two, that makes sense! A lot of this as soon as I see *how* to do it, it seems so incredibly logical in a deep sense sort of way, it's just figuring out that process that's tricky for me. I appreciate this help very much.

There are three things I'm still trying to figure out - I'm not sure which you can help with (and which I should post in a separate topic - the etiquette here I'm a little uncertain about with regards to that):

1) The characters box merely lists all of the characters that match the fandom (which, but there doesn't seem to be a way for me to choose *which* characters from that fandom are linked to that fanfic. For instance, for the first record, I want to pick the first three of the four characters because all three of those appear in that story. I was envisioning a dropdown situation pretty much the same as it worked for choosing authors and fandoms (the exception being that the options are limited to only the characters in that fandom and not all characters) - but for some reason it's not showing them as dropdowns and all my poking at the forms to compare them isn't turning up the difference. What am I not noticing there?

2) How do I sort the entries in the Add Characters box by fandom, and then character? Currently they're by whatever order I (or you) entered them - which isn't remotely alphabetical, of course. :) I wasn't even *trying* to be alphabetical when I started - just wanted to grab the first few that actually matched the stories I was testing with, and then a few extra entries to test the crossover & co-authored scenarios with.

3) How do I undo its memory of what characters have been entered? (Where is that even stored? I poked around but couldn't find that.) I tried to enter one that started with a J and it remembered the "jane doe" you entered (even though I changed the name of it to an existing character in the renamed fandom so it wasn't actually in the database anymore) and I couldn't get it to keep the capital J I entered except to type enough so it realized it *wasn't* the same name, and then use Home to go to the beginning and fix the letter. Fighting the auto-complete on capitalization is not my idea of fun, lol - if there's a way to clear it of anything other than existing entries, that would be awesome. (If not, that's not a huge deal, I guess - I just am always going to capitalize all of my characters so I'll have to fight it on any name starting with a letter that was first entered with lowercase.)

Thank you again for your wonderful help! It is exciting to see it closer and closer to functioning as I want it to. I attached my edit of the one you sent me so my new buttons and layout changes can be included in any future edits. (Removed as it's no longer relevant.)

Re: List box displays all relationships for all records

Posted: Tue May 28, 2019 9:20 pm
by UnklDonald418
Some answers to your questions
1) I'm still looking at that. It will likely require a query involving the Chars table and the Fanfic_Chars table.

2) On the Data tab of the Form Properties dialog is a Sort option. Select the button on the right with the ellipsis to open a sorting dialog.
Because EditChars is using the Chars table as its Data source try sorting first on "FandomID" and then on "Char". It should display the Fandom groups with the associated characters in alphabetical order. So far you have added the Fandoms in alphabetical order so it looks good, but as you add more entries to the Fandoms table the names may no longer be in alphabetical order.
If you need the fandoms sorted by name then you will need to use a query as the data source for the form EditChars.

3) On the Data tab of the Properties: ComboBox dialog for the column labeled Character change Type of List Contents from Table to Sql.
Select the box with the ellipsis that appeared to the right of List content to open a query design dialog.
In the dialog select the Chars table and then the Char field. Change the Sort property to Ascending then close and save the dialog. The query will be saved in the List content field.
Now you should be able to easily override the auto-complete feature of the Comb Box.
Another option is to replace the Combo Box with a Text Box. No drop-down list so you must type everything.

Re: List box displays all relationships for all records

Posted: Wed May 29, 2019 7:17 am
by Doranwen
I think I figured out how to implement #2 successfully - the only trouble is that replacing it with a query means I can no longer add any new entries! Fix one issue and create another, lol. I get why - a query doesn't let you actually edit like that (though maybe there are ways to do that? I seem to remember reading something about editable queries and them being problematic). Version 4 of the database shows that result. (Removed since to accommodate version 7.)

Version 5 of the database shows what happened when I tried #3 instead - which did seem to cure the auto-complete issue, at least! However, I'm still not sure how to get the characters in alphabetical order by fandom first and still be able to add new characters to it. (Removed version 5 as I decided not to go that route.)

I did have one idea, though - what if I take version 4 with the query (so I can see all of the fandoms and characters easily) - and change the name from Add Characters to View All Characters - and set up some other method of input for adding a single character - without needing to see a whole list in the input area? I'm not sure how that would be done, though, would have to do a ton of experimenting to try to see how to set that up right.

EDIT: I think I have it set up to work for me - I left the query as the data source for the EditChars form (now renamed ViewChars) and added a subform under that with a list box for fandoms and combo box for characters, with the list box sorted by fandom so at least the dropdown has them in alphabetical order. It seems to let me add new characters perfectly fine, and I was able to add a new fandom, refresh the ViewChars form (and thereby subform), and found it now in the list to use for adding a new character. :D (The auto-complete seems to have resolved itself to just characters currently in the database - it will auto-complete for "Jack Hudson" but not suggest "jane doe" - so maybe it needed a full restart of the computer to clear its memory of what had been entered? No idea, but at least it doesn't seem to be an issue.) Now just have to figure out how to get it to let me assign 0+ numbers of characters to a fic from dropdowns with the selection matching the fandom selected… Thanks again for all the help so far!

EDIT #2: I realized that after a while I would have too many authors - and while some only write for one fandom, several write for multiple ones, so I created a Fandom_Author table, a subform to link them - and I think it's all working, linked the four authors I had in there to test, and added a fifth in a different fandom to test that, so far so good. It'll need the same solution to limit authors for a fandom as will the characters - I figure once we work out how to assign characters to fics limited by fandom, then I can replicate the solution for the authors. (The only situation I would have to test at that point is a crossover - see how it handles an author who's assigned to both fandoms being listed once as the author.)

removed old copies of database as not relevant

Re: List box displays all relationships for all records

Posted: Fri May 31, 2019 6:17 pm
by UnklDonald418
Try the following on the form document Fanfic09 in Fanficv7
On the Data tab of the Form Properties dialog for the AddChars sub form change the Add data only property to Yes.
With that setting, the cursor will be placed on the add new record position on the table control for Add Characters. It should show a blank row and the navigator should show record 1 of 1.
Select a Fandom and then select or type a character name.
Press Enter and the cursor will move to row 2.
Press the Refresh button associated with ViewChars sub form.
The new entry should appear in the list in the table labeled View Characters, and the Add Characters table should be reset, ready for a new entry.

I've been looking at the issue around selecting characters being assigned to a particular FanficID value.
The normal listbox query would be

Code: Select all

SELECT "Char", "CharID" FROM "Chars" 
As you know that query lists all the characters, regardless of the Fandom. A way to filter the results of that query could be a query something like

Code: Select all

SELECT "Char", "CharID" FROM "Chars" where "FandomID" = the "FandomID" value displayed in FandomListBox.
I had previously seen creating a dynamic query using a coded macro. The problem with macros like that is that they aren't very easy to write or debug, they aren't very portable and they are easy to break.
I don't quite have have that ready for upload, but hopefully I will have some time later today to get it in a format you might be able to use.

Re: List box displays all relationships for all records

Posted: Sat Jun 01, 2019 7:51 am
by Doranwen
Ooh, I like the new entry thing - very nice! Though if I make a typo with the character name and hit Enter before I notice, the only real way to fix with that would be to go into the tables themselves - which I suppose is a valid fix since the only place that would have the actual character name would be the Chars table (everything else references the ID #). (I'm also unlikely to make such a typo, lol, so I think that seems quite workable for the extremely rare probability that I do.)

I did some searching before I saw your post and this solution for Access looks pretty much like the same SQL you were listing: http://blueclaw-db.com/comboboxlist/acc ... n_list.htm

So I'm reading up on SQL trying to learn how that works (particularly how to reference the other tables properly - that's the bit I can't quite figure out yet) so I can set up the right kind of query in the list box and see whether it'll provide the dropdowns again (because right now it does filter just fine - I just can't actually select anything). Thanks again! This is really helpful in guiding me towards my final goal.

EDIT: These two links look helpful: viewtopic.php?p=281715 and viewtopic.php?p=197512#p197512
I'm still wading through them to try to understand and see what applies to my situation.

Re: List box displays all relationships for all records

Posted: Sun Jun 02, 2019 3:58 am
by UnklDonald418
Books and online SQL teaching tools often express queries involving 2 or more tables using explicit joins. So it can be a little confusing for beginners because the Base query design wizard generates queries with implicit joins. For instance

Code: Select all

SELECT "Chars"."Char", "Fanfic_Chars"."CharID", "Fanfic_Chars"."FanficID", "Fanfic_Chars"."ID" 
FROM "Chars", "Fanfic_Chars" 
WHERE "Chars"."CharID" = "Fanfic_Chars"."CharID";
displays columns from both the Chars table and the Fanfic_Chars table. The join is accomplished with the comma between the two table names.
The same query using an explicit join might look like

Code: Select all

SELECT "Chars"."Char", "Fanfic_Chars"."CharID", "Fanfic_Chars"."FanficID", "Fanfic_Chars"."ID" 
FROM "Chars" 
JOIN "Fanfic_Chars" 
ON "Chars"."CharID" = "Fanfic_Chars"."CharID";
Concerning the uploaded modifications, the form document Fanfics10M has a table control with a listbox column titled Add Fanfic Character.
Select the list box and it will display a list of characters whose FandomID value matches the value for the Fandom shown/selected in the table with the column titled Fandom.
Select a character and press Enter.
If the cursor moves to the next line then that character has been entered into the Fanfic_Char table. Press the yellow Refresh button and the character will appear in the list of Selected Characters.
If the cursor fails to move that indicates that the character cannot be inserted, probably because that character is already on the list of Selected Characters.
Press the blue Reset button to clear the entry in Add Fanfic Character.

If you look at the Events tab on the Properties: List Box dialog for the column titled Add Fanfic Character there is an entry in the line When receiving focus. When the list box is selected it gains focus and the referenced macro is executed.

To look at the macro code select Tools>Macros>Organize Macros>LibreOffice Basic ( or just press <Alt> F11) to open the macro library manager.
On the left of the dialog select the + in front of Fanfic v8Mod01 to expand the tree.
Select the + in front of Standard and select Module1.
Select the Edit button on the right of the dialog to open the IDE window.
There are a number of lines preceded by an apostrophe. Those are debugging code that has been commented out.
Mostly they reference the MRI object inspection tool. MRI is supplied as an extension that provides good help when programming macros.
Generally it is best to harness the power of Base forms and queries rather than use coded macros, but in some cases macros do provide a good solution.
Fanfic v8Mod01.odb
(117.57 KiB) Downloaded 250 times

Re: List box displays all relationships for all records

Posted: Sun Jun 02, 2019 5:43 am
by Doranwen
Hmm, something isn't behaving like it's supposed to for me. No matter what fandom I have selected up above, the only characters shown in the dropdown are for FBI. Selected Characters still shows the entire list of whichever characters are available for the fandom assigned to the fic, in non-alphabetical order. I had the macro security set to Medium and then I set it to Low to test if that was the issue - both times it behaved the same. It *does* seem to save the character/fanfic matches, at least. I went and looked in the Fanfic_Chars table and it was overrun with matches, so I went and deleted all of them to make sure they weren't already assigned, before testing adding just Maggie and OA to the one FBI fic in there.

I tried to change the list box of Selected Characters to the query example you had below (actually, I tried to do that before, even) - but no matter how many ways I attempt it, it does not like the reference to the CharID column in Fanfic_Chars, keeps saying it doesn't exist (when I know that it does). Somehow it's not seeing that table (or else I'm referencing it incorrectly). Do I need to move the table to a different location using the Form Navigator or something, perhaps? Or am I missing something obvious?

Re: List box displays all relationships for all records

Posted: Sun Jun 02, 2019 7:07 am
by UnklDonald418
After selecting a new Fandom, try pressing the blue Reset button before selecting the list box.
The macro locates the controls using getByName() functions so any changes to the names of forms can effect the operation of the macro.
Try creating a query using the Create Query in Design View wizard with those two tables.
temp1.png

Re: List box displays all relationships for all records

Posted: Sun Jun 02, 2019 8:47 am
by Doranwen
The blue Reset button did do the trick - and later I didn't have to do that at all (after changing Fanfic10M to just plain Fanfic). I think my LibreOffice Base is very quirky sometimes - I actually had to rename the database v10 because v9 I had been playing around with before you posted your modified one - and when I deleted my version 9 and renamed yours to that, and opened it - it was my version 9 again! (I tried that twice, I think.) I still have no idea why, lol. So I'm not surprised anymore when things act weird for a bit before settling down.

I cleared out the Fanfic_Chars table again and this time loaded the chars for each fic. Interestingly enough, when I created a query like you showed - and set the listbox to use the query as the data source, then in the Column info told it to use Char for the data and bound it to column 4 (which was the column FanficID had)… when I hit refresh after selecting characters in the new box that used the macro, the Selected Characters box displayed exactly the number of characters I entered for that fic. The only problem? I can't see their names! The rows are blank. They're definitely there, it even says record #1, #2, etc. when I click on the rows, there's just absolutely no text associated with them. (I need a head-scratching smiley, lol.)

The only other thing I realized is… I'm not sure how to remove a character assigned incorrectly without going into the tables to do so. (Which would be difficult once I get enough fanfics entered - I'd probably have to create queries just to get the information I needed to figure out what row it would be to delete.) But that may be a side issue compared to not being able to see the ones entered. :)

EDIT: A friend of mine suggested trying the following SQL in the data source for the column:

Code: Select all

SELECT * FROM Chars  WHERE CharID IN (SELECT FanficID FROM Fanfics)
Weirdly enough, when she first gave to me, having forgotten the FROM due to her brain not being fully online, Base threw up an error (naturally) but then loaded the form and showed all of the character names. I fixed the FROM and it still showed the names - but threw an error over me having left the Bound Field at 4 (and referencing a table which didn't have five columns). When I switched the bound field back to 1… blank rows again. (Switching it to 2 gave numeric values, and switching it to 3 threw an error about no column 4.) I'm not sure what here is Base being inconsistent/buggy and what's me not figuring this out, lol. I can confirm that switching it back to Bound Field: 4 (which I left it on in the uploaded version) does keep showing the character names correctly, I just have to deal with it complaining about the nonexistent column…

EDIT #2: And I just added a new fandom, character, and author in order to test a crossover (because I had a hunch that it would not work well), and it is as I suspected. The Selected Characters box will only show the characters assigned to the fic that match the fandom from the selected row in the fandoms box. With two fandoms, if I had the top row selected, it only showed me the character from that fandom. If I selected the fandom on the second row, it only showed the character for that fandom. It's not impossible to use, but less than ideal. However, given that I can't reliably display the character names without throwing errors constantly, that's probably a pretty small issue to work out. It may not be possible to make everything display the way I want it to - and the trick here is figuring out what is reasonable to accomplish, what I actually need for functionality, and what I can live with not being "perfect". This one's pretty far down the list in importance, so not a big deal - only if it informs the way the general issue is solved (like "if I choose this route then I can do this whereas if I use the other possible solution then I can't").

Re: List box displays all relationships for all records

Posted: Mon Jun 03, 2019 2:28 am
by UnklDonald418
Sorry, the query I recommended was intended simply as a test, not as a list box query. A list box query needs to be fairly simple, it can't be much more complex than the one generated by the macro. In my tests any query that links more than one table together will not work in Link content of the control.

Currently the form document Fanfic uses the named query FanficCharsQuery to supply data to the SubSubForm named CharListBox. There are a couple of issues surrounding that query.
1. The only purpose for the query is to be able to sort the names in the list. If that is really important I recommend embedding the query in the form instead of using a named query, because it is too easy to inadvertently modify a named query and cause problems with the form. If you do use that query, either embedded or named, since the field Char is available there is no need for a list box in that table.
Note: The problem of missing names you mentioned was caused when you changed the column from a Text Box to a List Box control. You didn't change the Data field from Char to CharID.
2. Try creating a form where you use the table Fanfic_Chars as the data source. If you do that you will need a list box to display the character name, but the Link content query needs to be simple

Code: Select all

SELECT "Char","CharID" FROM "Chars"
The list won't be sorted but for a title that has more than one Fandom, all the characters will be displayed at once on the Selected Characters list. When using the query, only the characters associated with the Fandom selected in the Fandom table at the top of the form will be displayed.


The query

Code: Select all

SELECT * FROM Chars  WHERE CharID IN (SELECT FanficID FROM Fanfics)
returns a result set only by coincidence. It equates CharID with FanficID which in your database are not related. However a modification to the nested query

Code: Select all

[code]SELECT * FROM "Chars" WHERE "CharID" IN (SELECT "CharID" FROM "Fanfic_Chars")
[/code]
will generate a list of all the characters that appear in the intersection table Fanfic_Chars
Another slight modification, changing IN to NOT IN

Code: Select all

SELECT * FROM "Chars" WHERE "CharID" NOT IN ( SELECT "CharID" FROM "Fanfic_Chars")
will generate a list of characters that do not appear in the table Fanfic_Chars.
I thought that might be useful to limit the names that appear on list box selection lists. In that test I converted that query to a view and then used the view in the list box query. It did limit the names on the query list, unfortunately after a character appeared on one title it wasn't available for any other titles.

Re: List box displays all relationships for all records

Posted: Mon Jun 03, 2019 9:30 am
by Doranwen
Whee! It works! So I did the ultimate test - created the set of tables and related subforms and all that for Ships to see if I could replicate it all (a lot of copy/paste but comparing values and settings, etc., including a second macro to update the list box), and I can. :D Thank you so much! (You can see the result with v12.)

I realized one thing after I did all that, though - there is such a thing as a crossover ship. Unlike with characters, a ship can belong to two fandoms at once. Which would mean a many-to-many relationship - and yet another mapping table. So I did that - and did a bit of research to figure out how to join the tables correctly to modify the macro to match, and I think I got it! So I went ahead and did the same thing with authors. V13 shows the result there. I added at least one new record to test the crossover ship idea, and it works beautifully.

At this point, I think I have solved the major issue here. I have one more set of tables to do the same thing with as I've done with ships and authors - and now that I've done it with those, I can easily replicate that with the new attribute. I also have one other table to create, with a simple many-to-1 relationship between it and Fanfics (Fanfics gets a foreign key from it), so I'm not that concerned about the setup. I think with the amount of SQL I've picked up from trying things and looking at what you've fixed, I'll be able to figure it out.

Thank you SO SO much - you deserve a medal, seriously. I could never have figured out this on my own, for all the searching and poking around online that I had tried to do.

EDIT: I think I marked it Solved too soon - as I added more attributes, I realized that while I need the same filtering, it's only a many-to-1 relationship this time, so I'm wondering if there's a way to filter and have a dropdown or selection on a list for only one option? Attribute being time setting for the fic - while a fic can be set in more than one time if it's a set of snapshots, I'd just put "Multi" for the time setting and be done with it. Since the fic only needs a single time assigned to it (and I've set times unique to fandom because there are too many fandom-specific time markers), I set TimeID as a foreign key in Fanfics and created a Times table with TimeID as primary key, FandomID as foreign key, and Time as varchar field. (This is the first attribute that didn't need a many-to-many mapping table in some way, which makes it more confusing.) I can set up the time attribute the same way as I did the chars - but I don't need multiple times, so I wondered if it were even possible to do it differently, or does the fact that we're filtering by fandom negate any other possibility for setting this up? V14 shows what I tried - and I've done something wrong with the table that assigns the time settings to the fic - probably because the table setup isn't the same. I'm confused about exactly where to put the table, whether to use the macro (but how else can I filter and have a dropdown?), whether I set up the macro correctly… basically am not sure of what error I made with it.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Mon Jun 03, 2019 5:15 pm
by UnklDonald418
I've done something wrong with the table that assigns the time settings to the fic
That's one those silly errors.
On the Data tab of the Form Properties dialog for TimeListBox you have
Allow additions ............No
Allow modifications .....No
Allow deletions ............No
Add data only ..............No
which makes TimeListBox read only and effectively disables the list box control.
 Edit: I just noticed that once enabled the selected value is not saved in the Fanfics table.
Move the table control from TimeListBox to MainForm.
Once you have verified it is working you can delete TimeListBox 

Re: [SOLVED] List box displays all relationships for all rec

Posted: Mon Jun 03, 2019 5:38 pm
by Doranwen
No, that one's intentional - TimeListBox is supposed to just list the characters - it's the Selected Time Settings, just like Selected Characters and Selected Authors and all that, all of them have the nos all the way down. It's the one that I use to say "this time setting goes with this fic", called TCAddFanficTime, that I'm talking about. (I did realize I had it set Add data only = no by accident, which I would have figured out had it worked correctly - but that's definitely not the issue I'm talking about.) That's the one that has the edited version of your macro (so I could've made a mistake there), and for some reason it displays the TimeID numbers only, despite my attempts to tell it to pull the Time name from the Times table.

I would've liked to be able to use a single box that lets me use a dropdown to assign a single time setting to a fic - but I think I would've had no choice about filtering my options via the FandomID(s) that matched the FanficID for the given fanfic record, right? That's why you had the whole macro solution, to let me filter and yet also assign it to a fic. (Because I was able to filter, but not make any assignments, or assign, but not filter, but doing both seemed to be impossible without the macro.)

EDIT: Not that I couldn't have made an error with the TimeListBox too! (Since I can't assign a time setting to a fic other than manually in the tables, it's hard to test that.) I just know that setting those to No was not the error in question. It's just really confusing for me this time since there's no Fanfic_Times table (since a fanfic can't have more than one time setting, really), so I'm trying to figure out how to populate the field in the Fanfics table itself using the available TimeIDs (filtered by FandomID in Times that matches from the Fanfic_Fandom table) - but displaying only the Time values. Which should be easy with the SELECT "Time", "Time ID" bit but something isn't going right with it, and I'm not sure what. (Probably didn't help that I was trying to tinker with it in the wee hours of the morning, lol, but even in the light of day I'm not sure what to do.)

EDIT #2: Still haven't figured it out, but poking at it, I can see that's definitely the issue - the other TCAddFanfic*SubForm boxes all pulled from mapping tables that mapped between the fanfic and the attribute itself. Since this one has no such mapping table (because it's a many-to-1 rather than a many-to-many relationship) - and is instead trying to pull a single value from a filtered list based on the fandom chosen… (primary key / foreign key mapping) I can see where it's not going to behave right by copying the other settings as closely as possible. Something different is needed to make this one work - I just don't know what, lol.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Tue Jun 04, 2019 12:03 am
by UnklDonald418
The macros should each have a distinct name. You could use setListBoxListSource01, setListBoxListSource02 ...etc. You will need to reassign the Events to the new names before using the form.

Since TimesID is stored directly in the Fanfic table there is no intersection table which means things will work differently for this field.
Add a List Box control to MainForm. There is a wizard that should pop up to help setup the connections for that control.
Once the new list box has been added be sure to assign the correct macro to the When receiving focus event for the new list box.
Open the Properties: List Box dialog
On the Events tab be sure to select the correct macro for the When receiving focus event.
On the General tab change Dropdown to Yes if it isn’t already set.
You might want to change the Border and maybe the Background color. from their defaults.
That one list box control will display the current selection and allow editing/adding times.
To insure that the selection list is correct, select the Refresh button next to the Fandom table control.

If you watch the two table controls you have for Add Time Setting for Fanfic and Selected Time Setting as you step through the Fanfics, you will see neither of them work correctly.
That means AddFanficTimeSubForm and its controls are not needed, neither is the SubSubForm TimeListBox and its controls. In the Form Navigator delete both AddFanficTimeSubForm and the SubSubForm TimeListBox and the controls will be removed with them.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Tue Jun 04, 2019 2:10 am
by Doranwen
Yay! It works! I was able to add the last features I wanted (themes - which required a mapping table between fanfics and themes, but no linking/filtering by fandom - and the ability to assign some fics to series) without much trouble and fit it all nicely on the form (one might say "barely", lol). The attached v16 shows the final product. :D Thank you again! I can hardly wait to start entering data for real, and not just to test a feature here and there. :D

Re: [SOLVED] List box displays all relationships for all rec

Posted: Wed Jun 05, 2019 4:33 pm
by UnklDonald418
Congratulations on getting your database working to your satisfaction. A few more things you may not have considered.

The .odb file created by an Embedded Base database (look in lower left of the main database window) is actually a zip archive, which means all the tables, queries, forms, reports plus an early version of the HSQL database engine are all compressed into that file. It is handy for development and testing, but if anything goes wrong with the zip process the result can be a corrupt .odb file. So that you don't return here wondering "what happened to my tables?", be sure to backup frequently especially when doing data entry. Incremental backups are a good idea to minimize the effect of a corrupt backup.
To reduce the chance of losing the data, it is recommended to use a JBBC connection (also referred to as a Split database) where the tables are stored in separate uncompressed files along with a more recent version of the HSQL database engine. The queries, forms and reports remain in the .odb file.
Backup of a Split database requires backing up a directory/sub-directory structure with multiple files.

Conversion is not difficult.
Make a directory to hold your database, and copy the wizard into it.
[Wizard] Create a new 'split' HSQL 2.x database
Run the wizard to create the files and sub-directories.
Copy the tables, queries, forms and reports one by one from the Embedded version to the JDBC database.
Exit the JDBC database and rename the wizard file to a name of your choice. From then on that will be the odb file you will use.

Also be aware the the database engine does not do any housekeeping, so when you delete records wasted space in the tables will result.
After deleting records go to Tools>SQL and execute

Code: Select all

CHECKPOINT DEFRAG;
SHUTDOWN COMPACT;
Then close the database so the data will be saved with the wasted space removed.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Thu Jun 06, 2019 4:09 am
by Doranwen
I was able to follow all of that successfully - I think - but somehow all of the fields that were set to autovalue no longer do, and of course the split version doesn't let me edit the tables in design view. It looked like it was still possible to modify things via SQL, but I can't figure out what the command would be JUST to make a field autovalue (it's the right data type and there's existing data I don't want to mess up). How do I fix that? There's eight tables affected by this, as far as I can see (the rest are all junction or mapping tables with compound primary keys).

Re: [SOLVED] List box displays all relationships for all rec

Posted: Thu Jun 06, 2019 6:18 am
by UnklDonald418
The SQL command to set up an autovalue in a primary key looks like

Code: Select all

ALTER TABLE "MyTable" ALTER COLUMN "ColumnName" GENERATED ALWAYS AS IDENTITY (start with XX) ;
You will need to substitute a value for XX that is greater than the highest value in the primary key column of a table with existing records. If the table has no records then you can drop the phrase (start with XX)
The commands should be executed at Tools>SQL
Since you are working directly with the HSQL back end, you need to inform the Base front end of the changes. In the Tables area of the main database window select View>Refresh Tables.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Thu Jun 06, 2019 6:47 am
by Doranwen
That took care of that, thank you! … but I ran into another issue, lol. It can't find the macros. It actually locked up the form because it won't stop popping up this error message:

Code: Select all

A Scripting Framework error occurred while running the Basic script vnd.sun.star.script:Standard.Module3.setListBoxListSource03?language=Basic&location=document.

Message: The following Basic script could not be found:
library: 'Standard'
module: 'Module3'
method: 'setListBoxListSource03'
location: 'document'
How do I get the macros from the old version into this new one? The tree structure is a bit different - more options - and I'm not sure exactly where I'd copy the code to…

Re: [SOLVED] List box displays all relationships for all rec

Posted: Thu Jun 06, 2019 7:02 pm
by UnklDonald418
Since the macros are specific to the form document Fanfic they are stored in the database .odb file.
My Macros is a global library stored with the LO installation on one computer making it computer specific rather than file specific.

There are options for moving the macros.

Probably the simplest approach would be to export each module separately.
In the Embedded database open the IDE and select Module1, then select File>Export Basic (or there is an Export Basic icon on the Macro tool bar) and save the code from each module in a separate .bas file. Note the path to the directory where you are saving them.

In the Split database select <Alt>F11 and expand the tree for your database in the Macro From pane. When you expand the Standard library you should see a module named Embedded.
Select Embedded and then the Edit button to open that macro in the IDE. That is the code to connect your database to the tables and database engine so don't make any changes there.

Right click on the Embedded tab at the bottom of the IDE window and select Insert Module.
Select the new module (Module1) and delete the default empty macro that was inserted.
Then select File>Import Basic (or the Import Basic icon on the Macro toolbar) and navigate to your saved modules to import the code for Module1.
Repeat the Insert Module, Import Basic for the other 3 modules.
Save all the entries before attempting to use the form document in the split database.

The other option would require creating a new library and moving all the modules into the library. Then the library could be exported/imported as a single unit. Of course all the Events on the form document Fanfic would need to be changed to access the new library.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Fri Jun 07, 2019 12:22 am
by Doranwen
Aha! It took a little tweaking - turns out the button I needed was Insert BASIC Source (I tried the Import Dialog button but that didn't do anything). When I chose that it immediately gave me the four .bas files to choose from and clicking on the right one dropped the code in that I wanted. And for the benefit of anyone else looking at this for help, I didn't have any Export button or menu entry that said Export, but there was a Save BASIC icon that let me choose a location and save the modules (I had to manually type the .bas extension as I don't know that it would've put it on for me - it didn't seem to know what file extension I was working with). So it worked fine. :)

I thought I was back to normal but I noticed one weirdness - maybe it was present before but I hadn't tested enough? - I entered an author starting with a capital D and it was placed alphabetically after the number but *before* all the lowercase letters (including authors starting with a and c). I'm assuming that's because I set the field to varchar but not the ignore case variety? I figured I needed to do something like ALTER TABLE "Authors" ALTER COLUMN "Author" TYPE …and that's where I don't know how to write the proper type. My searches about SQL data types don't even turn up the ignore case variety (is that an OpenOffice only thing?) - and some searches I did said that they're all case insensitive, which is definitely not true in this situation, as it's ordering the authors in the dropdown (the one in Add Author to Fanfic - TCAddFanficAuthor) by numerals, then uppercase, then lowercase. I want it to ignore the case instead, though…

This won't be an issue with fandoms - all my fandoms are always properly capitalized - nor characters/ships - I plan on entering every character with a capital - but authors and titles both would need to be tweaked so that case doesn't matter.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Fri Jun 07, 2019 6:13 am
by UnklDonald418
The default sort order is by ASCII value. For the Authors list box you can use the UPPER() function in the ORDER BY clause.

Code: Select all

    strSQL= "SELECT ""Author"", ""AuthorID"" FROM ""Authors"", ""Fandom_Author""   WHERE ""Authors"".""AuthorID"" = ""Fandom_Author"".""AuthorID"" AND ""Fandom_Author"".""FandomID"" = " & sString & " ORDER BY UPPER(""Authors"".""Author"")"
The HSQLDB user guide says that they do support data type VARCHAR_IGNORECASE but they recommend against using it. I suspect that is due to portability considerations.
You can download a zip file from
https://sourceforge.net/projects/hsqldb/files/hsqldb/
Your Split database is probably using version 2.3.2 but you can verify that by running this query

Code: Select all

SELECT * FROM "INFORMATION_SCHEMA"."SQL_IMPLEMENTATION_INFO"
In the hsqldb-2.3.2.zip file, there is a guide.pdf file in the doc folder along with a html version of the user guide.
There are later versions, but there have been some reports from Base users of some minor compatibility issues beginning with version 2.4.0

Re: [SOLVED] List box displays all relationships for all rec

Posted: Fri Jun 07, 2019 6:51 am
by Doranwen
Ahh, OK. That was easy enough to modify in the module. Thanks! (The only case where the title matters is in queries where I'm sorting by title - I'll remember that for the queries I create, to put in the UPPER bit so they'll ignore the case when sorting them.)

Should I be changing the varchar_ignorecase ones I *did* set (when originally creating everything) back to regular ones or does it really matter, since I don't plan on using it outside of LibreOffice anyway?

Yes, mine's using 2.3.2 as you thought.

Re: [SOLVED] List box displays all relationships for all rec

Posted: Fri Jun 07, 2019 6:18 pm
by UnklDonald418
Apparently the data type VARCHAR_IGNORECASE was used in older versions of HSQLDB but has never been added to the SQL standard. For legacy considerations it continues being supported. Other database engines may not recognize that data type.
I suppose the LO developers might choose to stop supporting HSQLDB at some future date, but until that happens there is no compelling reason for using an alternative approach.