Filemaker Pro Advanced 11 - Lookups of my SQL DB
I have the following problem. I am setting up a form for looking up products in my MSSQL database. You can see the form and the relatioship in the following screen shot.
I am trying to lookup the product number and descritption from the IC_Products, which is my SQL database, and fill it into my promotion_lines.
The problem is that the MSSQL database uses a fixed field with empty spaces before the product code so say I have a product code11011, MSSQL sees it as " 11011"
So when I type in 11011 in my promotions maintenance, nothing is looked up because it is looking for "11011" without spaces. Do I have to do somekind of calculation to trim the data that SQL provides?
I was successful using value lists to look up the product, but I need to be able to manually type in product codes for quick entry.
Any help will be really appreciated!