Select batch of clients and apply tag to them

[Mickey12]

Hi
I would be most grateful for any help.
I am copying from an excel document and pasting into a table (Clients) icon. (FirstName LastName Email)
This works just fine with the fields in that particular table.
There is another table (Tags) which I then have to assign one by one to the newly added clients.
Is there a way to select a batch of clients and apply a tag to all of them?
Kind Regards
Mickey

[Hagar Delest]

You paste in a Writer table or another spreadsheet?

[Mickey12]

From a spreadsheet to a table

[Hagar Delest]

Writer table? Base table?

[Mickey12]

Base table

[robleyd]

Are you using the embedded HSQL database, or something else? If else, what is it?

[Mickey12]

Split. Hsqldb

[UnklDonald418]

More questions.
You told us a little about your Clients table, but what fields are in the Tags table?
We need to know how the two tables related.
From your question it sounds like multiple Clients have the same Tag, is that true?
Is there a field to store the Tag (or a link to a Tag) in the Clients table, or a field in the Tags table to store a link to a Client in the Clients table?
Does each Client have a single Tag or can they have more than one?

[Mickey12]

multiple Clients have the same Tag, is that true? True
field in the Tags table to store a link to a Client in the Clients table? True
can they have more than one? True
Thanks

[UnklDonald418]

multiple Clients have the same Tag, is that true? True
can they have more than one? True

That indicates that you have a many to many relationship between Clients and Tags, so

field in the Tags table to store a link to a Client in the Clients table? True

will not work
A many to many relationship requires what is sometimes referred to as an intersection table.
at Tools>SQL execute

Code: Select all

CREATE TABLE  "ClientsXTags"(
"ClientID" INTEGER,
"TagID" INTEGER,
CONSTRAINT "FK_CLID" FOREIGN KEY ("ClientID") REFERENCES "Clients" ("ID"),
CONSTRAINT "FK_TGID" FOREIGN KEY ("TagID") REFERENCES "Tags" ("ID"),
CONSTRAINT "PK_CTID" PRIMARY KEY ("ClientID","TagID" )
);

Each record in this table stores links into both a Client and a Tag, in addition the combination of the two is used as the Primary Key, which precludes entering duplicates.
Since that SQL command talks directly to the database engine, select View>Refresh Tables to inform the Base front end of the change.
I uploaded a little demonstration database to show how this table might work

Demo49_Tags.odb

(22.72 KiB) Downloaded 223 times

To add data to the new table I created several lists of Client names with the associated ID for each client, in temporary tables T1Client - T5Client. Each list for a different Tag. Then I ran a few commands at Tools>SQL

Code: Select all

INSERT INTO "ClientsXTags" ("ClientID", "TagID" )
(SELECT "ID", 0  FROM "T1Clients");

each time changing the SELECT statement to insert a new TagID and names from a different list.

Code: Select all

SELECT "ID", 1 FROM "T2Clients"
SELECT "ID", 2 FROM "T3Clients"

I stopped there so you can try inserting records from T4Client and T5Client. You could reuse those tables for different Tags or you can make new tables with names of your choosing.
There is a Form document Clients-Tags that demonstrates how intersection tables can be used on a Base Form.
Hopefully, that will show you a way to solve your problem.

[Mickey12]

Just what I needed.
Thank you for your time.
Regards

[Mickey12]

Struggling a bit to get the code to run on my base

[RoryOF]

Please explain what is happening or not, and what error messages you get.

[Mickey12]

Of Course! Sorry
1: user lacks privilege or object not found: ID in statement [CREATE TABLE "ClientsXTags"(
"ClientID" INTEGER,
"TagID" INTEGER,
CONSTRAINT "FK_CLID" FOREIGN KEY ("ClientID") REFERENCES "Clients" ("ID"),
CONSTRAINT "FK_TGID" FOREIGN KEY ("TagID") REFERENCES "Tags" ("ID"),
CONSTRAINT "PK_CTID" PRIMARY KEY ("ClientID","TagID" )
)]

[UnklDonald418]

The error message is reporting that it cannot find a field named ID in one of the tables.
I suspect the problem is with this line

Code: Select all

CONSTRAINT "FK_CLID" FOREIGN KEY ("ClientID") REFERENCES "Clients" ("ID"),

Try changing ID in "Clients" ("ID") to the name of the Primary Key in your Clients table

PS
The .odb file from you split database isn't terribly helpful.
Since we don't have the other files that make up the database we can't connect to any of the Tables, Queries or Forms.
Although, I was able to peek into the zip archive, where I did find a reference to "Tags"."ID" which is why I concluded the problem is likely with the Clients table.

[Mickey12]

Hi I managed to create the ClientsxTags table. It was indeed needing the Primary Key name in my Clients table. Thanks
I then ran
INSERT INTO "ClientsXTags" ("ClientID", "TagID" )
(SELECT "ID", 0 FROM "T1Clients");

I substituted Clients for your T1Clients
I get error
1: user lacks privilege or object not found: ID

I also tried this
INSERT INTO "ClientsXTags" ("ClientID", "TagID" )
(SELECT "ID", 100 FROM "Clients");

Same error message
1: user lacks privilege or object not found: ID

What am I doing that is not correct?

[UnklDonald418]

Your table T1Clients doesn't have a field named "ID" so the query

Code: Select all

SELECT "ID", 0 FROM "T1Clients"

fails
Replace ID in the query with the name of your Primary Key

[Mickey12]

Thanks for all the input but I cannot get this to work. I have no "T1Clients"
If I could get a form to show a list of all clients with a corresponding entry capability for tags next to each name I could then at least easily do this albeit on an individual basis.
When I tried creating this form it only shows names with existing tags and not those without tags

[UnklDonald418]

When I tried creating this form it only shows names with existing tags and not those without tags

That indicates that you are using the table ClientsXTags on the MainForm.

In the example I uploaded look at the form document Clients-Tags. On the left, the upper table lists all the clients regardless of the number of tags associated with that client because that form design uses the Clients table as the data source for the MainForm.
Select a client from that list and the lower table displays any tags associated with that client.
Select a row in the lower table displaying a tag and you can use the list box control to edit which tag will be used for that row.
If you don't want that tag you can delete that row..
If you want to add another tag, select the first blank row, then use the listbox to select a new tag.
All of that is possible because the SubForm uses the table ClientsXTags as the data source.
But that requires selecting the tags one by one, client by client.

Your original question concerned adding tags in batches.
Let's try a different strategy from the one I used in my example. Copy the Clients table into a temporary table, to keep this simple use the name TClients.
at Tools>SQL execute

Code: Select all

ALTER TABLE "TClients" ADD COLUMN "Select" Boolean; 
UPDATE "TClients" SET "Select" = FALSE;

then select View>Refresh Tables to tell Base about the change to TClients

Use the Form Wizard to create a new form using TClients as the data source, and use a table control to display the data.

Open the new form and put a check mark in the box in the Select column for each client you want to add the Tag that has an "Tags"."ID" value of 0.
close the form and at Tools>SQL execute

Code: Select all

INSERT INTO "ClientsXTags" ("ClientID", "TagID" )
(SELECT "ID", 0  FROM "TClients" WHERE "Select" = TRUE);

You will need to substitute the name of the Primary Key in your TClients table for ID in the above statement. Be sure to save the edited statement for later use.
If you want to clear all the checked names, then at Tools>SQL execute the statement

Code: Select all

UPDATE "TClients" SET "Select" = FALSE;

Open the new form and put a check mark in the box in the Select column for each client you want to add the Tag that has an "Tags"."ID" value of 1.
close the form and edit the saved INSERT statement, the only change you should now need to make is the 0 to 1
Then at Tools>SQL execute the edited statement.
Repeat the process for each "Tags"."ID" value.

[Mickey12]

I really appreciate all your trouble in trying to provide a solution. I am away for a few days and will tackle this next week.
Don't want you to think that I am ungrateful.