1 2 Previous Next 16 Replies Latest reply on Jun 20, 2010 9:11 AM by LuisN

    Can this be done?

    LuisN

      Title

      Can this be done?

      Post

      Hey Guys, its getting there.. this time i am actually re-doing everything so its a lot more organized.

       

      Ok, so what i have now , i want to know if this can happen.

       

      When choosing a "Product Number" its a drop down menu. i have a field in my Portal that auto fills the Description of that Product Number that i chose. Now, there is another Drop down menu called Ref #, and this pulls a Ref # drop down list which works. Now, the Description which is the SAME field that auto fills when choosing Product Number but this time b/c im choosing a Ref #, it auto fills from another Table inside the same DB.

       

      Right now the Description field has Inventory::Description and Auto fills when choosing Product #.

      I want tried doing

       

      Inventory::Description or Ifempty (Inventory::Product Number) here is where i wish there was a button that said THEN Inventory Special Orders::Description to be filled.

       

      is that possible to do a THEN? if a field product number is empty??

       

      did i explain that well?

        • 1. Re: Can this be done?
          Steve Wright

          I think I follow, you will have to excuse me if not, its a little late here..

           

          It sounds like you need along the lines of :

           

           

          Case (
          IsEmpty(Inventory::Product Number) ; Special Orders::Description ;
          Inventory::Description 
          )

           

           

          Which is basically saying, if the product number from the inventory table is empty, then use the description from the special orders table, otherwise use the description from the inventory table.

          • 2. Re: Can this be done?
            LuisN

            Lets see if that will work. How do i do that thou with something that is a selection and not a "script" like a button.

             

            Ok take back the top what i said. i placed it in the actual field as a calculation b/c thats how its getting it before. but it didnt work =(

             

            i did

             

            Case (
            IsEmpty(Product Item Number) ; Inventory Special Orders::Description ;
            Inventory::Description
            )

            • 3. Re: Can this be done?
              LuisN

              Im also looking at the case of what it needs. can you explain this a bit?

               

              Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )

               

              if its not to late , if it is. ill wait till sm1 else responds like phil :)

               

              so in turn i tried this

               

              Case ( IsEmpty(Product Item Number) ; Inventory Special Orders::Description ; Inventory::Description )

               

              and that didnt work either =(

              • 4. Re: Can this be done?
                Steve Wright

                 


                LuisN wrote:

                When choosing a "Product Number" its a drop down menu. i have a field in my Portal that auto fills the Description of that Product Number that i chose. Now, there is another Drop down menu called Ref #, and this pulls a Ref # drop down list which works. Now, the Description which is the SAME field that auto fills when choosing Product Number but this time b/c im choosing a Ref #, it auto fills from another Table inside the same DB.

                 

                Right now the Description field has Inventory::Description and Auto fills when choosing Product #.

                I want tried doing

                 

                Inventory::Description or Ifempty (Inventory::Product Number) here is where i wish there was a button that said THEN Inventory Special Orders::Description to be filled.

                 

                is that possible to do a THEN? if a field product number is empty??

                 

                did i explain that well?


                 

                Let me just clarify the above..

                 

                If you choose only a Product Number, you want the description from the inventory table (if it exists)

                If you choose a Product Number AND a Reference, you want the description from the Inventory Special Orders table ?

                 

                Or

                 

                If you choose a Product Number, which does not exist in your inventory table, you want it to check the special orders table for a description instead ?

                 

                Or

                 

                Am I completely missing the point :-)

                 

                Also, is it only the description field you are working with, or will there be more fields such as unit price which need to be looked up ?


                • 5. Re: Can this be done?
                  Steve Wright

                  Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )

                   

                  Curly brackets show optional parameters..  With case, you can include multiple test / results in one statement

                   

                  for example : 

                   

                  Case(

                  field = 10 ; "field was equal to 10" ;

                  field = 20 ; "field was equal to 20" ;

                  field = 30 ; "field was equal to 30" ;

                  "field did not match any critera"

                  )

                   

                  Edit : You can write the code as above in Filemaker, without needing to fit it within a single line, makes reading it easier.

                  • 6. Re: Can this be done?
                    LuisN

                     

                    Ok so you wrote.

                     

                     

                     

                    If you choose only a Product Number, you want the description from the inventory table (if it exists)

                    If you choose a Product Number AND a Reference, you want the description from the Inventory Special Orders table ?

                    Or

                    If you choose a Product Number, which does not exist in your inventory table, you want it to check the special orders table for a description instead ?

                     

                    What i need is to have the choice of the Description, Price, Deposit Required, Amount, all to be filled in If i choose either 

                    Product Number then all the fields load which product # comes from 1 table

                    OR

                    I choose

                    Reference # which then loads all the fields.

                     


                     

                    Also, is it only the description field you are working with, or will there be more fields such as unit price which need to be looked up ?

                    First yes, there will be more fields that will be looked up and from 2 different tables in the same DB.

                     

                     

                    • 7. Re: Can this be done?
                      Steve Wright

                      Here is a sample .fp7 file I quickly made of how I would probably do this (but I may change my mind in the morning)  :-P

                       

                      Sample File

                       

                      Edit : However, that wont take into account if the end user types a product code, then types a reference...

                       

                      Obviously, im not sure on how your data is stored or set up, but wouldnt it make more sense to have the item status, such as "special order" in the inventory table. that way, the user wont have to decide which field to use.  

                      • 8. Re: Can this be done?
                        LuisN

                        Wow wow.. ok for some reason yours works JUST fine. but 1 thing :)

                         

                        the 2 tables are being pulled from another DB now that cant be hard. and i have the relationships setup JUST like u do and it still doesnt work =(

                        • 9. Re: Can this be done?
                          LuisN

                          DUDE! i got it to work. for some stupid reason the code said table missing when i put the correct one. BUT i was playing around with the relationships sSOOOOo it works now! thank you!. mark as solution!

                          • 10. Re: Can this be done?
                            LuisN

                            Ok Now 1 last thing.

                             

                            I have the following for Deposit, b/c its needed. i want to calculate the Deposit * Quantity filled out.

                            i have the following code.

                             

                            Case (
                            not IsEmpty(Product Item Number) ; Stock From Inventory::Deposit Required * Quantity;
                            not IsEmpty(Ref Number) ; Special Orders From Inventory::Deposit Required * Quantity
                            )

                             

                            does that look right, ? b/c its not working :)


                            • 11. Re: Can this be done?
                              Steve Wright

                              Happy to help..

                               

                              p.s you have to mark as solution if you feel it is.

                              • 12. Re: Can this be done?
                                LuisN

                                Sorry not sure if you saw my last post before yours. :)

                                • 13. Re: Can this be done?
                                  Steve Wright

                                  Instead of calculating directly from the inventory tables, you will want to lookup deposit required field (as you are with description)

                                  If for instance, the deposit required field changed in the inventory tables, doing it your way would re-calculate all documents on your system

                                   

                                  In which case, line items table would then have a field for deposit required, which is auto calculated, with do not replace field contents checked.  Then add a new field for "Deposit amount" containing the following calc

                                   

                                  Deposit Required * Quantity

                                   

                                  • 14. Re: Can this be done?
                                    LuisN

                                    DUH! and thats what i did with sub-totals. god sometimes working till 1am doesnt work! ok i gotta keep going im on a roll b/c of you :)

                                     

                                    but you prob have to go to sleep =(

                                     

                                    ok if u have room for 1 more question..

                                     

                                    So all of that was from a "Proposal"

                                    now i want to move from here to an Invoice

                                    the Invoice will be in the same DB but a different view.

                                     

                                    i want to be able to do 2 things. Either

                                    A) Click on a button to swtich me to Invoice Form view and it takes everything thats in the Proposal Form view

                                    B) Make an invoice without having to choose Proposal if i have to sometimes.

                                    1 2 Previous Next