12 Replies Latest reply on Jul 3, 2013 6:24 AM by willrollo

    Triggering a result from a line itum

      Title

      Triggering a result from a line itum

      Post


           Hi

           Not quite sure hwo to describe what I want to achieve, but shall do my best.

           I have my invoice solution. Part of this incudes the usual invoice details, invoice, customers, products and line items table.

           When the user is on the invoices layout creating an invoice, I would like certain things to happen depending on what product is chosen in the line items portal. For example, If Delivery is selected, then I want a field on the invoices table to show "yes" > i have managed this far. But where i have a problem is if this item is removed from the invoice, then I want the 'Yes" field to reset to "".

           DOes this make sense? I can supply any more info that may be required..,.

           Thank you

        • 1. Re: Triggering a result from a line itum
          ninja

               If the field in question is an unstored calc field set to:

               If (DeliveryField = "Delivery" ; "Yes" ; "" ) it would clear when the line item is removed or when delivery was deselected.

               Is an unstored calc appropriate for the rest of the function?

          • 2. Re: Triggering a result from a line itum

                 Hi Ninja - Thank you.

                 As an exmple, (as a few different records create different results) I have a field called "Upholstery". If a line item called "com' is selected then the Uphosltery field automactically says "On order".

                 Once the material arrives for that customer, the user can then click in that uplostery field and select "recieved" from the drop down menu (that contains the data, stock, recieved and on order). Upholstery at the moment is just a text field. I did try uasing calcs but this prevented me the user from updating it...

                 This is bacically the problem - I want the user to be able to manually update the field that is altered by the record selected in the invoice lines - and also of course to revert back to the default field data if that record is deleted...Quite tricky to exlain! Thank you for your patience..

            • 3. Re: Triggering a result from a line itum
              philmodjunk

                   The field that user manually updates should not be the field with the calculation. Can you take another try at giving an example, but this time indicate which fields are from the invoice table and which are from the line items table?

              • 4. Re: Triggering a result from a line itum

                     Sorry - been a bit busy!

                     I have attached my relationship diagram where it shows the relevent table occurances. I hope this is enough.

                     On the layout in question (based on Invoice details) i have a portal to invoicedetails_INVOICEITEM.

                     If the user selects from a drop down, certain products,  a script will be triggered. This script is below:

                       
                •           If [ invoicedetails_INVOICEITEM::Item = "BR" ]
                •      
                •           Set Field [ INVOICE DETAILS::Due_Date; INVOICE DETAILS::Due_Date + 21 ]
                •      
                •           Set Field [ invoicedetailsLISTITEMS::Care; "Bronze" ]
                •      
                •           End If
                •      
                •           If [ invoicedetails_INVOICEITEM::Item = "L" ]
                •      
                •           Set Field [ INVOICE DETAILS::Due_Date; INVOICE DETAILS::Due_Date+ 14 ]
                •      
                •           End If
                •      
                •           If [ invoicedetails_INVOICEITEM::Item = "DEL" ]
                •      
                •           Set Field [ INVOICE DETAILS::Include_Delivery_Paragraph; "Y" ]
                •      
                •           End If
                •      
                •           If [ invoicedetails_INVOICEITEM::Item = "SL" ]
                •      
                •           Set Field [ INVOICE DETAILS::Schedule Upholstery; "Stock" ]
                •      
                •           Set Field [ INVOICE DETAILS::Upholstery; "Stock" ]
                •      
                •           End If
                •      
                •           If [ invoicedetails_INVOICEITEM::Item = "COM" ]
                •      
                •           Set Field [ INVOICE DETAILS::Schedule Upholstery; "Awaiting" ]
                •      
                •           Set Field [ INVOICE DETAILS::Upholstery; "Awaiting" ]
                •      
                •           End If
                •      
                •           If [ invoicedetails_INVOICEITEM::Category = "Plate" ]
                •      
                •           Set Field [ INVOICE DETAILS::Plate; "1" ]
                •      
                •           End If
                •      
                •           If [ invoicedetails_INVOICEITEM::Item = "SOL" ]
                •      
                •           Set Field [ INVOICE DETAILS::Schedule Upholstery; "Special" ]
                •      
                •           End If

                     As you can see, they are similar in nature.  Even if the target field is not a calculated field, I still do not know how to reset the target field to what it was if a line is altered or deleted..

                • 5. Re: Triggering a result from a line itum

                       diagram

                  • 6. Re: Triggering a result from a line itum
                    philmodjunk

                         Sorry, but that doesn't fully answer my question:

                         If Delivery is selected, then I want a field  (1) on the invoices table to show "yes"

                         In what table is "Delivery" defined?

                         "a field" is defined in what table?

                         Trying to see if the one to many relationship between Invoice Details and Invoice Items is an issue here or not. If this all takes place with in a single Invoice Item record, a simple auto-enter calculation may work quite well for you.

                         If we can replace this with calculation fields, your solution will be simpler and values shown can update automatically when you edit fields in the line item table. (And yes, we can do this and still enable the user to edit fields like you describe.)

                    • 7. Re: Triggering a result from a line itum

                           Apologies. The first example I provided was a very basic overview.

                           If "L" is selected (from drop down menu in the portal) from the field invoicedetails_INVOICEITEM::Item, then I want Invoicedetails::DueDate to be the date already set as Get(Current)Date+40 from a previous script PLUS 40. And thus, if this line is deleted or altered, then I want it to revert back to what it was before the L was added as a line.

                           The example for "delivery"  - If 'del' is selected from drop down then I want INVOICE DETAILS::Include_Delivery_Paragraph; "Y"..

                           Is that any clearer?

                           I see what you mean re the calc fields - but am pretty sure I tried that but it didnt work ..but will wait to hear your thoughts first before attempting it again...Thank you

                      • 8. Re: Triggering a result from a line itum
                        philmodjunk

                             I don't see a need for a script here. Calculation fields should work for you. What I see in your script is that the field that the user is modifying:

                             invoicedetails_INVOICEITEM::Item and invoicedetails_INVOICEITEM::Category

                             Are not the fields where the value then changes. None of the fields being modified by set field modify either item or category. Thus, a straight forward field of type calculation should do the trick.

                             If, on the other hand, any of the fields being modified by a set field step in your current script also need to be modifiable through a direct user edit of the field, you can probably set up that field as an auto-entered calculation.

                             If all else fails, you can set up one field for user editing a value and put a calculation field on top of it that returns a value based on item, Category and the user editable field hidden beneath it. (Use behavior settings to block browse mode access to the calculation field that sits on top.)

                        • 9. Re: Triggering a result from a line itum

                               Thank you Phil,
                               I shall try out your suggestion next week when I have a moment. I am pretty sure I tried that type of thing orginally and it didnt work which is why I triewd scripting, but I could be wrong as it was a while ago that I did this part of my solution..

                                

                          • 10. Re: Triggering a result from a line itum

                                 Hi Phil

                                 I have changed the Due Date field to a calculated field  (date)- so it contains the following:

                                 Case(

                                 invoicedetails_INVOICEITEM::Item = "Br"; Get(CurrentDate) + 49;

                                 Get(CurrentDate) + 40).

                                  

                                 However, if I add a line item in the invoicedetails_INVOICEITEM portal that has the item field data "Br", I get the message dialogue saying

                                 "This action cannot be performed because this field is not modifiable."

                                 What I want is the invoice Details::Due Date field to be 49 days from today's date. But if this line is changed to another item code then I just want it to be what was in the field before as I have other scenarios that I want to add to this but there could be one or two used at the same time - If i had more than one and deleted one then the date woudl revert to todays date instead of the previous calculated date..

                                 Hope all that makes sense!

                                  

                            • 11. Re: Triggering a result from a line itum
                              philmodjunk

                                   You should not be getting that error message. Making Due date a calculation field does not make the item field unmodifiable. There's some other detail invovled here not visible in what you have posted here.

                                   

                                        But if this line is changed to another item code then I just want it to be what was in the field before

                                   I don't see how your calculation returns the date to the previous date--especially since the date returned by Get ( CurrentDate ) will be different unless this all takes place on the same day.

                              • 12. Re: Triggering a result from a line itum

                                     Hi
                                      Phil

                                     You are correct - my second part of the calculation does not revert field to original date - I wanted to do that once I have the first part sorted. I shall look into why it isnt allowing me to alter it..