prefill from dynamic details

Are you using the registration add-on and a separate form for user registrations? If so, you're totally in business.

Does the data go directly into a DB or do I need an add on for that?

All Formidable data goes straight to the DB.

Just for clarity, it sound like you using "prefill" in place of "autopolpulate" meaning you want user meta data collection at the time of registration to autopolpulate specified fields in a form.

If this is what your trying achieve I have integrated this heavy into my application process. Visit brandtroops.com/register to complete an mock application. Notice how all the information from your profile form autopolpulates other forms. If this is what your trying to achieve, you can use the Formidable pro registration add-on to save user meta and autopolpulate other fields.

Getting the logged in user details is easy, most form systems do this and I have this working with other plugins, and I assume formidable does this too. I want to use wordpress as an internal staff management system, lots of webforms to collect specific information, reporting on this etc etc, I have another plugin that lets me retrieve DB information to edit in the front end via tables.

The management team need the ability to fill out a form and have it fill in the staff members information. For example, when a staff member calls in sick, the person who takes that call fills out the webform, searches for the user (drop down, or simply enter in the staff ID into a field) and then the form fills out the other info such as name, email, manager name, manager email etc (fields that I can enter in as user_meta).

I currently have Oracle APEX running this application, on the change of a field (entering info) it runs multiple sql queries and fills in the other fields.

The other idea I had was to seperate the user search function, they search for a user (lots of profile systems - maybe formidable has something like this) and I embed a button that links to the URL of the form, using URL pass through to fill in user details. Open to any other methods to do this.

If I am understanding correctly, what you are asking for is doable, but may be a little complex. While a user is filling out a form on the front end, you want a real-time update of staff information based on the value of a lookup field or a direct entry text field. If you look at this recent post, https://formidableforms.com/help-desk/with-lookup-simple-text-field-watch-lookup-fields-is-hidden/, the information says that the single line text fields you would need to receive the staff data in real-time, can't be auto populated from a lookup field. This means you are relegated to using a jQuery Ajax call or the REST API.

The jQuery side of things is simple. The jQuery function that watches the lookup field for changes is added to the form's after fields area in the Customize HTML screen. The PHP side that does the heavy lifting can be added to functions.php or another php file that is included in the theme's functions.php. You would use the wp_ajax_ and wp_ajax_nopriv_ actions to allow WordPress to receive the Ajax call. The PHP side executes your custom SQL query to retrieve the required information from Formidable's frm_items and frm_item_metas tables. Formidable follows the same design schema as WordPress, using two tables to store all form entry content.

If you're not familiar with writing Ajax code, you may want to consider hiring a developer who can. Ajax can be tricky to debug.