Problem with Lookup Field and Instant Web Publishing
Hope to get some help regarding an issue we're having using IWP (Instant Web Publishing).
There could be a number of ways to resolve it, perhaps by creating a more complex calculation inside our database instead of looking up values from a related database. I'm wide open to suggestions.
We're using FileMaker to enter order data from e-biz. Each staff person works remotely, and would enter this either via Remote Connection or IWP.
Depending on what "Marketplace" a product sells on, there are specific merchant fees that apply, which are factored into the Net P&L calculation. Some marketplaces have zero transaction fee, others are merchant based fees, such as $.30 per transaction and 3%.
Currently, we have 2 related databases:
1. Order Entry (our main database)
2. Marketplace Data (contains a record for each marketplace, transaction %, and transaction fee ($.0)
Amazon.Com: 0% and $0.00
Our Web Store: 3% and $.30
In our Order Entry database, the "Marketplace" field is a dropdown selector and is a Lookup from the Marketplace Data. So, if "Our Web Store" is chosen, it auto-populates the 3% transaction percentage value and the $.30 transaction fee in those respective columns of Order Entry. So, we have a calculated field for overall transaction fees that is something like this:
This works perfectly ... except, via Instant Web Publishing. You can see the Marketplaces in the drop down, but regardless of what Marketplace you choose when submitting via IWP - none of the lookup values populate. (Transaction % and Transaction Fee fields are empty).
If you use FileMaker Pro to access via Remote Connection, it works as designed.
So ... my question (finally) is this:
* Is there ANY WAY to get this to work via IWP? (Clearly, we're trying to prevent buying a license of FMP for every staff user who adds data remotely!)
*This could be totally resolved if the lookup info wasn't required - but how to build a formula that has so many variables is way beyond my understanding: ie. "If Marketplace=Amazon, Transaction Fee=0 and Transaction %=0, but If marketplace=My Web Store, Transaction Fee= $.30 and Transaction % = 3%", etc ...
We've got about 6 various marketplaces with differing transaction fees and %.
Sorry for the overwhelming back-story ... just want to give you all the most info possible.