AnsweredAssumed Answered

Problem with Lookup Field and Instant Web Publishing

Question asked by fredmckinnon on Apr 6, 2011
Latest reply on Apr 6, 2011 by philmodjunk

Title

Problem with Lookup Field and Instant Web Publishing

Post

Hey Gang,

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.

Background:

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)
Examples:
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:
Commission=Gross*TransactionPercentage+Transaction Fee

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.

Thanks,

Fred

Outcomes