Set Latest SermonID Counter / Index?

I have to migrate my site onto a new Joomla instance (SermonSpeaker 5.9.2, on Joomla 3.9.16]. Because I have about 500 sermons, I moved all the SermonSpeaker data manually using SQL inserts -- being careful to make sure Series, Users, and Speaker IDs match.

After doing this, all the sermons correctly appear in our SermonSpeaker backend, however:

• listing sermons in the frontend fails ("Sorry, unfortunately I didn't find any Sermons")
• if I try to create a new sermon, it does so with SermonID = 0, not the next index [in my case 509]

I assume there must be a stored value in the database somewhere that indicates what the "last sermon ID" is; but I can't find it anywhere. Can anyone help me find it?

(I've done this exact process about 3 years ago with another site migration, so I know I must be missing something obvious.)

The issue with the sermon (and series and speakers) ID is that this ID is an auto-increment column.
You can set the start value for that auto-increment with this command: Instead of "tbl" you use the name of the sermonspeaker tables and instead of "100" you use the maximum ID in the table and add 1 to it. In your case "509".
Do the same also for the speaker and series tables as you will likely face the same issue there.

The frontend likely has the issue that the category IDs don't match anymore, or the asset table doesn't match the categories anymore.
Easiest way is probably to create the categories fresh so Joomla will create all the needed stuff. And then you either batch assign the new categories to the sermons/speakers/series or you manipulate the sermonspeaker tables directly and replace the old catid with the new one.

Thank you so much. In the end, this was user error (which your tip to AUTO_INCREMENT) clued me into: when importing the PREFIX_sermon_xxx tables, I did not run the SQL commands:

to:

• create indexes for those tables;
• increment the primary keys;

.... as these commands were found way down in the SQL export file.

After I reran the import (fixing catIDs and userIDs to match those in the new CMS instance); and being sure to execute the above, previously-neglected commands, everything works perfectly.

Great to hear that you were able to fix it!