Portal Row Numbering Issue

[skearton]

Hi,

 

Originally I built my line items for a quote to just use the record number symbol to automatically number them sequentially. My users want to be able to change the line item number and sort it ascending and move line items around as needed.

 

I have created a field "LineItemNumber" to hold the value and I have the portal sorting ascending based on those values.

 

My problem is some users forget to add line item numbers so I need a way to set the numbers automatically if they do.

 

Also a user may have entered some item numbers on a quote, and go to add another new item, but forget to set that new line item number for the new item. Is there a way to set the missing number needed without changing the numbers they have already entered?

 

Thanks for you help!

[eos]

Not sure if this exactly what you need, but at least it might give you some ideas to fiddle with; check out the auto-enter options for sort number in the LineItems table.

ResortLineItems_eos.fmp12.zip

[MikeKD]

There's a Portal line shift demo on this page that might help you out...

http://www.excelisys.com/filemaker-tips-tricks-demos-downloads.php

[LaRetta]

Hi Skearton,

 

Do Users really need to change the order of the LineItems?  I would think the business would want the products, freebies, payments etc in a certain order on their Invoice lineitems - most have specific preferences in this regard.  You are marking the records and it won't always be dependable in multi-user mode without some solid error-trapping in your script.  

 

It would be far easier if you simply specified the sort of the lineitems in the order according to your business rules and then use, from the FM menu, Insert > Other Symbol > Record Number, placing it in the LineItem row of the Invoice.  Then the issue of flagging the fields, not flagging fields, and requiring Users to keep them in order will disappear. 

 

Just another option ... :-)

[MikeKD]

I have a database that has songs in it for each gig. It would be handy to be able to change the order without deleting and adding new ones.

Will have a look at Record Number now though.....

[skearton]

Hi eos,

 

I tried your auto enter calculation that you showed in your demo, and I'm still not managing on getting it to work. The auto enter calc is a number and I do not have the "do not replace existing value (if any) checked off. I'm not sure if I missed something but I did read elsewhere that if you have a field that starts with an underscore you have to use the Quote function in order for SQL to recognize it.  I modified the statement to do that since my foreign key field does start with an underscore.

 

I noticed in your demo that one has to leave the field within the portal (i.e. commit it) in order for the calculation to enter a number. I tried that too, but I still get no value, not even a "?" that sometimes shows up if an executeSQL function is not calculating correctly.

 

Can you see what I might be missing in the auto enter calculation below? My tables are "Quote" and "QuoteLineItem". My foreign key for the "Quote" in "QuoteLineItem" is "_kf_QuoteID".

 

QuoteLineItem: LineItemNumber =

 

Let (

 

[

 

existing = ExecuteSQL ( "SELECT Count (*) FROM QuoteLineItem WHERE Quote ( _kf_QuoteID )   = ?" ; "" ; "" ; Quote ( _kf_QuoteID ) ) ; // counts all items of this quote (including itself)

 

myMax = ExecuteSQL ( "SELECT MAX (sortNumber) FROM QuoteLineItem WHERE Quote ( _kf_QuoteID ) = ?" ; "" ; "" ; Quote ( _kf_QuoteID ) ) ; // calcs the max value of field sort number of all line items of this quote

 

newMax = myMax + 1

 

 

] ;

 

Max ( existing ; newMax ) )    // this seems to be the all-around desired behaviour; gap-filling would require a recursive CF

[eos]

Yes, this quote/quoting business can be a bit confusing (it is for me!); 

 

existing = ExecuteSQL ( "SELECT Count (*) FROM QuoteLineItem WHERE Quote ( _kf_QuoteID )   = ?" ; "" ; "" ; Quote ( _kf_QuoteID ) ) ; // counts all items of this quote (including itself); first quote OK, I guess; second isn't

 

I don't know about the quoting of the field name, I guess that's OK (I stopped using leading underscores). The problem I see is your Quote() ing the parameter.

 

The thing to understand here is that the SQL part is only the string within the first pair of quotes; the other parts (field-/record-delimiter, optional parameters) are conveniences of FileMaker ExecuteSQL(). So you can use any valid FileMaker expression to calculate the parts outside of the SQL string. If you Quote() the field name you use as parameter, FileMaker resolves the field reference, but puts it in quotes, and that will be the cause of the calculation not working.

 

btw, it's a good debugging practice to test calculations in the Data Viewer; you can test out an entire calculation, or just try it bit by bit, to better see where exactly an error originates.

[skearton]

Thanks I will look at this again. How do you use the data viewer to test a calculation? I've never done that before.

[eos]

Open the Data Viewer, select the Watch tab, and click the green plus icon to add a new expression.

 

http://fmhelp.filemaker.com/fmphelp_12/en/html/fmpa_tools.24.4.html#1035402