Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Portal record creation- something funky


jzn
 Share

Recommended Posts

I've got this infuriating issue with creating records via a portal, maybe someone can clue me in as to why this is happening.

 

I've got a portal set up, i create records by entering data in a new row. The related table is matched via a serial number field, so when I enter new data, it creates a new related record with the proper serial number. Or, so it seems, until I notice that none of the relational calculation fields in the portal-linked record update immediately. It acts as if the relationship hasn't kicked in.

 

But, when I look at the related record, the match field has been added properly.

Yet, the relational calculations remain zero, as if there was no connection to another table. I can browse other records, do finds, run scripts, and when I come back, the offending record is still sitting there like an unrelated lump.

 

The weird part is that it does figure itself out, eventually, and the related calculations just start working suddenly! I can't figure out what the circumstances are that wake it up. It seems to kick in when I least expect it.

 

I did create a kludge that works, a button that repastes the same serial number into the match field. It just copies the serial that is already in the field, and pastes it over itself. That always gets the calculations working. But I can't leave this "whack the side of the tv" button in the final database, it would seem unprofessional!

 

Does anybody have any guesses as to why the relationship doesn't kick in immediately? Or, maybe a better solution than my kludge button? thanks guys...

Link to comment
Share on other sites

When you start entering data, a record gets created but the values of its fields haven't been "committed" until you click out of the related record. Try clicking into the portal, type a couple fields' worth of info, then click outside the portal. That should commit the related record and you'd then see all the auto-entered data appear.

Link to comment
Share on other sites

Thanks so much for the reply!

 

I was thinking it was along those lines, but I'm unsuccesful in fixing it that way. I can create several records in the portal, and none of them kick in, even when I flip through records, create new ones, or create a button for them that runs the 'commit record' script step. It's madness, i tell you!

Link to comment
Share on other sites

I've also tried clicking in and out of the ID field, but the only thing that gets the relationship to immediately kick in is when i actually re-enter the ID (or when I just cut and paste it back into itself).

Link to comment
Share on other sites

Also, I don't actually have the ID field displaying in the portal, it just gets created in the background. That couldn't be the cause of the issue, could it?

Link to comment
Share on other sites

Your description is not all clear (what does "relational calculation fields" mean?). There's a known issue with child records not looking up/auto-entering data from parent record (or thru the parent record), if the parent record itself is not yet committed. Unstored calculations using data from the parent should work fine.

Link to comment
Share on other sites

thanks for replying, comment!

 

Your description is not all clear (what does "relational calculation fields" mean?).

I meant a calculation field that references a related field.

 

There's a known issue with child records not looking up/auto-entering data from parent record (or thru the parent record), if the parent record itself is not yet committed. Unstored calculations using data from the parent should work fine.

 

I'll look again, but I'm pretty sure that there are no lookups or autoenters involved, only unstored calculations.

Link to comment
Share on other sites

I don't want anybody's eyes to glaze over, but I'll try to describe the problem very specifically:

 

Setup:

 

3 tables are involved- PURCHASE ORDER, COMMISSION RATES and COMMISSIONS

 

In the PURCHASE ORDER table, there is a shopping cart-style list of items. They are summed up by a field called TOTAL.

 

In the COMMISSION RATES table, there are 4 separate commission rates listed for each salesperson, in percentage form (for example, .05, .15, .25, .30). Each salesperson has individual rates.

 

In the COMMISSIONS table, there is a pulldown called COMMISSION IN WORDS that allows you to choose the sales rep's commission rate using text descriptors like 'full commission' and 'half commission'. A field called ACTUAL COMMISSION RATE then looks at the COMMISSION RATES table to translate the proper commission percentage from the text choice.

 

Finally, in the COMMISSIONS table, there is a field called COMMISSION OWED that multiplies PURCHASE ORDER:TOTAL by COMMISSIONS: ACTUAL COMMISSION RATE. COMMISSIONS is matched to ORDERS by a field called PRODUCT ID.

 

Problem:

 

So, there is a portal to COMMISSIONS in the main layout for PURCHASE ORDERS. You can create new records in commissions from the portal. When you use the pulldown menu to select the commission type, it immediately returns the proper rate in the ACTUAL COMMISSION RATE field. However, COMMISSION OWED remains uncalculated.

 

At that point, if I go to the layout for COMMISSIONS, I see that the record exists, the commission is chosen, and the PRODUCT ID matches up fine. However, the database has not yet linked properly with PURCHASE ORDERS, as COMMISSION OWED remains uncalculated, and a portal linked to PURCHASE ORDERS does not display the related record.

 

Solution:

 

In order to get the two records properly linked, thus displaying the COMMISSION OWED calculation, I can do one of 3 things.

1- I can retype or copy/paste the PRODUCT ID into COMMISSIONS table, and it kicks in. (I now have a button that does this)

2- I can go to define fields and make any change, and it kicks in.

3- I can do lots of other stuff in the database, and it will kick in at unpredictable times.

 

I wonder if I have a field in COMMISSION RATES set as a lookup instead of a caculation field, causing this weirdness. Other than that, I can't imagine what the problem is. It seems like a bug in filemaker, but I know that is unlikely.

Link to comment
Share on other sites

I tried to reproduce this (assuming "shopping cart-style list of items" means a portal to POitems), but it works just fine for me. The rate in Commissions should of course be a lookup (in case the rates change), but that shouldn't make any difference.

 

All in all, it sounds like just a screen refresh issue. This happens often when a relationship depends on a key calculated via another relationship. I don't know why you should face this issue given what you describe, but one cure for this is Refresh Window [Flush cached join results]. There may be a more elegant solution, that wouldn't require pressing a button, but for that I would have to understand the root of the problem.

Link to comment
Share on other sites

I tried to reproduce this (assuming "shopping cart-style list of items" means a portal to POitems), but it works just fine for me. The rate in Commissions should of course be a lookup (in case the rates change), but that shouldn't make any difference.

 

All in all, it sounds like just a screen refresh issue. This happens often when a relationship depends on a key calculated via another relationship. I don't know why you should face this issue given what you describe, but one cure for this is Refresh Window [Flush cached join results]. There may be a more elegant solution, that wouldn't require pressing a button, but for that I would have to understand the root of the problem.

 

Tremendously helpful post, comment! I'm glad it works the way it should for you. Especially glad to hear that the lookup for rates is a working component, and very impressed that you cobbled this together based on my still incomplete description.

 

Sadly, it's not a screen refresh problem. As i mentioned, it persists across field changes, layout changes, mode changes, et al. Yet, simply re-typing or pasting the Product ID into the commissions table fixes it.

 

Has anybody heard or seen similar behavior of any kind? I've got a few unused tables left over in the database, and some duplicate relationships, clutter from previous endeavors. My current guess is that the problem is somehow related to the clutter, although ill be damned if i can find any reference points.

Link to comment
Share on other sites

As i mentioned, it persists across field changes, layout changes, mode changes, et al.

 

That is NOT what you said earlier:

 

2- I can go to define fields and make any change, and it kicks in.

3- I can do lots of other stuff in the database, and it will kick in at unpredictable times.

 

Well, we could sit here for a long time and make guesses, but I think that without seeing an actual demo of the problem this is not going anywhere.

Link to comment
Share on other sites

That is NOT what you said earlier:

 

Sure it is.

I can browse other records, do finds, run scripts, and when I come back, the offending record is still sitting there like an unrelated lump.

 

You see, it kicks in specifically when I define fields, or when I repaste the ID number, or at other, more random times. It does not simply kick in whenever i refresh the screen. It can't be a 'commit' problem because I can browse other records, and come back to a blank Commission field.

 

I've never filed a bug report to filemaker before, but I'm thinking maybe I should, if I can't find a more logical explanation. Before I do, I'll put up a copy of the database for you to take a peek at, if you are interested.

 

http://www.worldofjasoncraft.com/echo4troubleshooting.fp7

There's the database. It's very messy on the inside, but I have included some descriptors, pointing at the problem.

 

The database should open up to the payment tab of an order form, with $500 paid on the order. The sales rep's commissions reflect the $500. Try adding a sales rep, choosing their commission rate, and watch the thing not calculate. You'll see that the relationship doesnt seem to be working initially, even though the correct match field is in place in the commissions table.

Link to comment
Share on other sites

oh, one more thing I should mention...

 

there is an invisible button right now, hovering over the offending calculation field, so that if you 'tap' it, it corrects itself. The button runs a script that repastes the ID number into the commissions field. The point is, i dont want the users to have to tap the thing to get it to work.

Link to comment
Share on other sites

AFAICT (it's hard wading thru the maze), the actual rate is two tables away, and you need that relationship to work in order to work out the rate. I have no idea how to fix this, because your overall approach is not how I would have done it, and I can literally feel my mind twisting when I try to understand it.

 

I think you have to STORE the commission data in the Commissions table, because if the rates ever change, all your history will go down the drain. That is a radically different approach to what you have now, and I believe your time would be beter spent on reworking your basics than on the refresh problem.

 

BTW, Refresh Window [Flush cached join results] does work.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use