ODBC help please. finding an emlpoyees name from external database
Hi, i am tasked with a project to build an application for an employee to receive goods into an external database via ODBC on their own personal iPhone/iPad. Also, I am new to FM and sql. So the first part of my task is to request an employees 3 digit number in FMgo (could also maybe scan their staff card barcode to get the number), find that number in an external employee database, grab their corresponding name, bring it back into FMgo and subsequently display their name on each layout throughout the remaining process of receiving goods for that day. So, my understanding is i store that user name as a string that i can insert into a merge field that i can put in each layouts header. This process only really needs to be done at the start of each day or if a new user wishes to pick up the device and receive some goods. The problem is due to lack of experience, i have no idea how to implement this. I have experimented with importing this employee list and the problem with that is having to import a new table every time someone joins/leaves the company. I have ODBC set up and FMserver set up also. So, im guessing i put a new field down on a layout that a user can enter their employee number but wont we need to create a new record for that? is there any way to do all this prior to creating records? Then im guessing we store that number as a string somehow (eg "mycode") and try executesql().
select top 1 name from aims.COD where type = 'e' and code = (mycode)
The problem is i dont know how to get the result from this sql query and store it as a string to use later on. Also, i will need to update the external database with the employees details later on. ie who received these goods, and this stored string will be used again later on in the project.