Values in empty portal row

[normanicus]

Hello,

 

A client reported that his wife, who does the accounts, noticed an extra row was appearing in her invoices. This row showed two fields containing '0' (zero) but formatted as 2 place decimal.

 

When I investigated I saw that the empty portal row in the 'Sales Items' portal where the original items are created has these two values set. First shot was to go to the Sales Items and see if there was a phantom one. No.

 

Once invoiced the items show in a file 'Invoice Items' and these are listed in another portal on a different layout. I produced what I consider to be a kludge fix by turning on portal filtering and doing this:

 

not IsEmpty ( Sales Items::Code )

 

which should filter out all records that should not be there. It worked.

 

Now, I thought, this must mean that an extra phantom invoice item is being created. But this is not the case.

 

I then went back to the original 'Sales Items' portal and turned on hiding for each of the offending values. This hid them but did not stop them showing up in the invoice.

 

What is going on?

 

The solution started in FM5 so these portals have been there a long time. One client alternates between FM12 and FM13. Does FM13 do anything to fmp12 files when it first opens them?

 

I assume I am missing something simple.

[Ocean West]

It is recommended that you do not cross 12/13 solutions as 13 opens 12 it will add more meta data to a layout - where it may misbehave in 12 and most certainly not work  if you apply any new features of 13. 

 

is this extra row appearing in invoices in the portal - i am assuming it's not the last row the blank one that is used to add new items - that is there by default 

 

what keys are you using between invoice & line items? something that the user has ability to change - perhaps they orphaned the children records and are now showing up because they change the invoice number - you should use Get ( UUID ) as the primary key between invoice & items. making sure that that is not available to the end user to edit. 

[normanicus]

Thank your for your reply. The client could, in theory change the things you mention but has not.

 

Yes, in the sales items portal, records can be addd in the blank line there by default.

 

I was wrong in part of my description. There are phantom invoice item records being created. I don't understand how this is possible as they are created from the sales items and there is not a phantom record there. However, there are a lot of these phantoms invoice items that have an invoice number but no job number linking to the transaction that created them. This leads to the possibility that the problem has long existed (and down thus, no doubt, to an error on my part) and that what is new is that they are appearing. I do not think this is the case.

 

The only clue I have is that I introduced some script triggers in the sales items portal. These enable the client to enter targets - target discount, target price and target reduction - and they all up date each other via these script triggers. He then has a button to use the targets for the sales item. It appears the zero values started to appear in the default, empty row after these changes.

[comment]

I was wrong in part of my description. There are phantom invoice item records being created.

 

If I follow your description (big IF!) someone or something is creating empty invoice item records, not phantom records (whatever that means). If they have an invoice number, then most likely they were created via the relationship from Invoices - assuming this relationship is defined to allow creation (you're not telling us the important stuff).

[normanicus]

You are right, I am not telling you the important stuff. That is not because I'm withholding it but discovering it.

 

In fact, every sales invoice has one of these records with nearly all values empty. The same script also handles hire invoices, repair invoices etc but it is only the sales items (from which invoice items are created) that are added via the default empty portal row.

 

But, these have not shown up in invoices till very recently. What has changed is that a value appears in the empty portal row before record creation. This value is appearing in those 'phantom' invoice items. For a reason I have not yet identified this has made them turn up in the invoice print out.

 

To distil all this the root of the problem, and what appears to be a potential FM bug, is that values are showing in the empty portal row before a record is created there.

 

The existence of the unwanted invoice items is due to an error on my part.

[comment]

values are showing in the empty portal row before a record is created there.

 

Values are not showing in a portal row. Values are showing in a field. So take a look at that field and how exactly it is defined - that being the important stuff here: Is it a calculation field? If yes, is it unstored? If yes, did you uncheck the "Do not evaluate if all referenced fields are empty" box?

 

If you answered yes to all three, you may very likely see a 0 in the first empty portal row before a new record is created there.

[normanicus]

Is it a calculation field? Yes.

Is it unstored? Yes.

Is "Do not evaluate if all referenced fields are empty" unchecked? No.

I also checked if any of the fields on which the calculation depends have that unchecked. No.

 

But how can a value be showing in a field before the record exists? 

 

You say, "Values are not showing in a portal row. Values are showing in a field."

 

Excuse me, but if a meal is in my stomach it is also in me. Thus, if value is in a field that value is in a record. Referring to the portal row in this instance seemed appropriate as the empty portal row is not a record, it the visualisation of metadata showing a structure into which data can be entered.

[comment]

You say, "Values are not showing in a portal row. Values are showing in a field."

 

Excuse me, but if a meal is in my stomach it is also in me.

 

True, but there is a difference in the current context of troubleshooting; one formulation leads to identifying the problem, the other does not. Same as telling a doctor "the pain is in me" rather than "the pain is in my stomach".

 

 

But how can a value be showing in a field before the record exists?

 

Well, I already gave you one example of how this can be "accomplished".  Now you say that the field is not set to evaluate when all referenced fields are empty - so you must have something in the field that forces it to evaluate nevertheless. Still following the trail of the "important stuff", what is the exact formula used by the field (please include also a short description of the referenced fields)? Alternatively, just wrap the formula in a Case ( not IsEmpty ( ChildID ) ; <your formula> ) statement and move on to better things.