11 Replies Latest reply on Sep 4, 2012 12:28 PM by philmodjunk

    Date Range and "If" formula

    Michelle_1

      Title

      Date Range and "If" formula

      Post

      Hello friends!

       

      I need a little help with one little problme :)

      I have a 2 global date fields for a time frame search that look like that:
      Date 1 _____
      Date 2 _____

      i need a Field that "grab" this "date range" so that i can use it for a "If" formula like this one:

      If ( Orders_Date = "date frame"; Orders_Commission ; "" )

      It would then show me all orders of the worker in this date range :)

       

      Txx in advance!

        • 1. Re: Date Range and "If" formula
          philmodjunk

          Do you want to perform a find or list records in a portal?

          Both can be done with your two global date fields.

          Here's how to do it in a script that performs a find:

          Enter Find Mode[] --> clear the pause check box
          Set Field [YourTable::Orders_Date ; YourTable::Date 1 & "..." & YourTable::Date 2 ]----Date 1 and Date 2 can be defined in any table, but must be global.
          Set Error Capture [on] ----> keeps "no records found" dialog from interrupting your script if no records in the date interval are found
          Perform Find []----> no find criteria is specified in this step.

          • 2. Re: Date Range and "If" formula
            Michelle_1

            The find script is fine,

            have a Status field so my reports show me all organized, like:

            worker 1 sold in 10.Sept. 1 Contract for 10$ Status: OK

            Worker 1 sold in 12.Sept. 1 Contract for 15$ Status: Cancelled

            in the end of the month my DB should get all he sold "-" the cancellations and tell me how much i should pay.

            Therefore i make a search by time frame and worker and i get a report with the results, all working 100%

            the problem is that in the end its not right...

            for example:

            The worker sold 1 contact for 10$ in one month

            and the customer cancelled it.

            instead of having then a 0$ in his bill he gets then -10$

             

            so i need to make first one calculation field that shows me first all that he sold (independent of the Status, if its cancelled or not) and then in the end i get all the cancellations out.

             In my point of view, the Calculation would really look like If ( Orders_Date = "date frame"; Orders_Commission ; "" )

            Another little problem is that i need to find it out today, cuz if i dont give it ready tomorrow, then its all gone, its a bad bad deadline :( i fly away to Brazil and all the work was for nothing...

            Here is a pic of the part of my table that i mean...  and then instead of this "c_Value_Gutschrift" i would use this If ( Orders_Date = "date frame"; Orders_Commission ; "" )

            My god i cant look at this DB anymore, im even glad its over tomorrow...

             

            Thank you very much!

            • 3. Re: Date Range and "If" formula
              philmodjunk

              Not being able to read the language used (German?) limits my ability to understand what you posted in your screen shot.

              Is each order and each cancellation a separate record?

              In other words, if Item A is ordered by Customer John Smith and then John later cancels it, do you have one record for the order and one for the cancellation or would both order and cancellation data be recorded in the same record?

              Do you have the typical Orders---<LineItems>-----Products/Services

              Relationships or something else? (the names aren't important, it's the function and structure of these tables that are critical.

              • 4. Re: Date Range and "If" formula
                Michelle_1

                Thank you so much for your attention, yes its German, i get dizzy myself with this language :)

                 

                I have just one record, and when the custommer cancell then i update the status in this record.

                I have the typical orders - Line up - Services structure.

                 

                maybe instead of chage status, i should find a way to duplicate the record with another line up iten that makes the negative comission? ui that sounds unclever at first view...

                 

                the way it is is all working perfect, the relationships, the searches and all sweet and smooth...

                 

                In my screenshot, "Boni, Sprung, Widerruf" = different kinds of cancellations.

                And Gutschrift = OK. that means i pay for it.

                When i add a new order, it comes automatically as "OK" (Gutschrift) and when somebody cancell i change from OK to "Boni or Widerruf or whatever reason the custommer cancelled)

                Then i have the summary Fields and calculated Fields to show me the Total of each one. Those Fields i have on my Report, so that when i make a time range search it shows me just the Total for this period of time.

                But as i said in the end is wrong cuz i get all cancellations as a minus and not as a "0"

                 

                Tx ^^

                 

                 

                • 5. Re: Date Range and "If" formula
                  philmodjunk

                  Define a calculation field as:

                  If ( Status = "Gutschrift" ; OrderTotalFieldHere )

                  A summary field that computes the total of this calculation field will total all "OK" orders and all cancelled orders will not be part of this total.

                  You can also set up:

                  If ( Status ≠ "Gutschrift" ; OrderTotalFieldHere )

                  and a summary field that totals up this calculation field will give you the total value of all cancelled orders. (Might be useful to know how much potential income was lot due to cancellations)

                  There are also ways to set up a list view report layout of these records where you sort your records by status and use a summary field that computes the total of your OrderTotal field and get a sub total of each by using a sub summary layout part "when sorted by status".

                  • 6. Re: Date Range and "If" formula
                    Michelle_1

                    Yes but then when i make

                    "A summary field that computes the total of this calculation field will total all "OK" orders and all cancelled orders will not be part of this total.A summary field that computes the total of this calculation field will total all "OK" orders and all cancelled orders will not be part of this total.

                    Then when i change the status from "OK" to Cancelled it would just make a "-" and it would be wrong again, or?

                     

                    so i solved it somehow...

                    I made so:

                    If (My_table_Worker_Number = g_Worker_Number ; Order Total Field ; "")    This is the Total sells of the worker all kinds of Status together

                    g_Worker_number = the global Field i use on the search

                    so it takes ALL he sold, calculate it as "plus" and later take all the cancellations away.

                    So that when he has 1 sold - 1 Cancel = 0

                     

                    The other way before, when i changed from OK to Cancel it just ignored the Order and make just a negative, like 1 sold - 1 Cancel = -1 (because the OK Order does not exist anymore since i change from OK To cancelled)

                     

                    omg i am so tired that my brain dont wanna work anymore!

                     

                    txtxtxtx for your attention, and btw... the layout with the Total of cancellations is now included in my DB its really a very good idea, so that i can realize how much we lose and also the most common reasons for cancellations!

                     


                    • 7. Re: Date Range and "If" formula
                      philmodjunk

                      Yes but then when i make

                      "A summary field that computes the total of this calculation field will total all "OK" orders and all cancelled orders will not be part of this total.A summary field that computes the total of this calculation field will total all "OK" orders and all cancelled orders will not be part of this total.

                      Then when i change the status from "OK" to Cancelled it would just make a "-" and it would be wrong again, or?

                      What I am describing should not create any negative values--at least not in the fields I've described. I don't know in what calculations you would use these.

                      • 8. Re: Date Range and "If" formula
                        Michelle_1

                        Oh i get it now, but as a good newbie i am, my DB is abit confuse :)

                        I have also other Summary fields that calculate

                        (Sum of OK) - (Summ of Cancell)

                        and there then i get the bad bad minus.

                         

                        Sorry for messing up your time abit ^^

                         

                         

                         

                        • 9. Re: Date Range and "If" formula
                          philmodjunk

                          Well

                          (Sum of OK) - (Summ of Cancell)

                          is a calculation field, not a summary field.

                          And will compute the total of all OK records in your table - the total of all cancelled records in your current found set. I don't see the usefulness of this figure.

                          • 10. Re: Date Range and "If" formula
                            Michelle_1

                            hmm

                            it happens very often that the custummer cancel his contract in the next month. that means i paid already the worker and i need then to get the cancellation from one month ago...

                            therefore i have one extra date field that i fill when i change the status from something i already have paid (from ok to cancell)

                            so i need this search that shows me the sold items just from the current month, but also the cancellations from the current month + cancellations from older records.

                            i also need to store in another table the results of the search, for the case of one worker getting in one month a negative bill then in the next month i must make his actuall bill - the negative ammount from the last one.

                             

                            its really a confused business here ^^

                             

                             

                            • 11. Re: Date Range and "If" formula
                              philmodjunk

                              Definitely confusing to read.

                              It seems from here that all you need to do is include those records in your found set for the calculated values to be correct. Using an extend found set or an additional find request where you specify the date in that extra field would seem to be the added detail that you need here.