0

Formula

Hello,

So I have a questoin about how to create a proper formula. I use the information from the formula and copy it so that I can text it to the driver. This is how it has to look like (image below). The problem is that when I have several things in the "Pick Up Info" table it just jumbles it all together, attacheted below also.. I would like to seperate every line from the table. Any way this is possible?

Thanks

 

Screen Shot 2019-02-20 at 1.37.46 PMScreen Shot 2019-02-20 at 1.59.35 PMScreen Shot 2019-02-20 at 1.42.01 PM

26 replies

null
    • Mconneen
    • 5 yrs ago
    • Reported - view

    You say you "text it" to the driver.   How about "emailing" it to them.. but use their SMS as the "to" ? 

    For example, my current provider is AT&T... so I can "email" 2195551212@txt.att.net

    Which AT&T will then "text" me..

    I suspect there are other SMS services you could subscribe to.. 

    Finally .. What about registering each driver on your Ninox Cloud ... then you can simply send them a quick text to check their app.. and they can use their iPhon / Android device to pull up your app that has all the info? 

    WOW.. Lots of different solutions.. :) 

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Yea, the problem is not in the way I deliver it, I already send it as text using a gateway. Its the way I arange the content -main issue. It piles allmy addresse toether if more then 1 in the table

    • Sean
    • 5 yrs ago
    • Reported - view

    Is the first image a mockup? It would be helpful if you explained what your formula is. You can find some text manipulating functions here - https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language. The substr() function might be helpful.

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Here is an image of the formula.  My problem is how to organize the formulaa if I have multiple forms in the Pick Info table?

     

    Screen Shot 2019-02-21 at 7.29.00 AM

    • Sean
    • 5 yrs ago
    • Reported - view

    I can't play with it and I don't have anything similar, but it looks like you will need to test for the number of records returned by the Pick Up Info table and the Delivery Info table. A question in that regard is will they always be the same number? Quantity Pick Up records = Quantity Delivery records? Another question is if there is more than one record will they each get their own *Reply **Y** to confirm receipt* message.

     

    Having said that, you'll need to use loops to keep the information grouped correctly and I'm guessing it will take 3, but that's based on the information I've seen so far.

    • Sean
    • 5 yrs ago
    • Reported - view

    Another question... Is there a common field that connects the Pick Up and Delivery information? Load # or Driver?

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    The "reply Y" is just at the end of message - it is not crutial. But it is usually an unequal ammount of records, 'Pick Up Info' can have 1-2 and 'Delivery Info' may have up to 8. How do I create loops? Im not very good with this code stuff. lol

    Basically What I need is to have the Address seperately for each record in the 'Pick up info'/'Delivery Info' tables.

    • Sean
    • 5 yrs ago
    • Reported - view

    No worries, I don't know how much time you get to try things out, but it's pretty satisfying when you figure something out. I'm at Scott's in Marysville, OH waiting to be loaded 😆

     

    First, before we get into loops, are all the Pick Ups grouped together and separate from Deliveries? Is there a common field like Driver, Truck or Load?

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Hahah, you a trucker?I love when it all just works perfectly so I spend quite some time on this. Pick Ups are a separate subtable and Deliveries are as well.  Linked by common load 

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Hahah, you a trucker?I love when it all just works perfectly so I spend quite some time on this. Pick Ups are a separate subtable and Deliveries are as well.  Linked by common load 

    • Sean
    • 5 yrs ago
    • Reported - view

    Yep, for about 20 years. Just got loaded and going to try to get to Knoxville before all the parking spots are taken. I’ll look at it some more after I get there.

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Thank you!

    • Sean
    • 5 yrs ago
    • Reported - view

    So, what's going on is the reference for Pick Up Info and Delivery Info is returning an array and that's why it's combining the information. What you'll have to do at the top of your formula is define 2 variables and I'll just use these as examples...

     

    pupCount := count('Pick Up Info');

    delCount : = count('Delivery Info);

    "***** Pick Up *****"  + "

     

    Here's the loop part and you'll have to use it on each section that has the reference in the formula. I'm just using the Pick Up as an example here and the loop goes through each index in the array and displays the information.

     

    " + for i in range(0, pupCount) do
    item('Pick Up Info'.'Customers/Consignees'.Name, i) + "
    " + item('Pick Up Info'.'Customers/Consignees'.Address, i) + "
    " + item('Pick Up Info'.'Customers/Consignees'.City, i) + ", " + item('Pick Up Info'.'Customers/Consignees'.State, i) + "
    " + "
    "
    end

     

    Make a duplicate database so you can play around with the formula and not worry about breaking anything. If you have questions, I'll do my best to answer.

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Hey,

    So I've tried this and it says this: 

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Hey,

    So I've tried this and it says this: 

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Screen Shot 2019-02-25 at 1.15.00 PM

    • Sean
    • 5 yrs ago
    • Reported - view

    oops! There should be a "let" in front of the variables 😬...

     

    let pupCount := count('Pick Up Info');

    let delCount : = count('Delivery Info);

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    So I was able to figure this out. But I can't get it to work properly. When there are 2 picks It loops everything over . I dont know how to create the  proper formula so that it has the diliveries on a separeate loop

     

    again....Screen Shot 2019-02-25 at 5.24.18 PMScreen Shot 2019-02-25 at 5.23.55 PM

    • Sean
    • 5 yrs ago
    • Reported - view

    Move one of the "end" statements above "***** Delivery *****". That should fix it because it will finish the first loop, which is pickups, and then go on to the delivery loop. As it is coded now, the delivery loop is inside the pickup loop.

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Does nothing

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Does nothing

    • Sean
    • 5 yrs ago
    • Reported - view

    This works for me. I'm not sure where you are off, but you can paste this in your formula and make adjustments.

     

    let pupCount := count('Pick Up Info');
    let delCount := count('Delivery Info');
    "***** Pick Up *****" + "
    " + "
    " + for i in range(0, pupCount) do
    item('Pick Up Info'.Name, i) + "
    " + item('Pick Up Info'.Address, i) + "
    " + item('Pick Up Info'.City, i) + ", " + item('Pick Up Info'.State, i) + "
    " + "
    "
    end + "***** Delivery *****" + "
    " + "
    " + for i in range(0, delCount) do
    item('Delivery Info'.Name, i) + "
    " + item('Delivery Info'.Address, i) + "
    " + item('Delivery Info'.City, i) + ", " + item('Delivery Info'.State, i) + "
    " + "
    "
    end

    • Sean
    • 5 yrs ago
    • Reported - view

    Screen Shot 2019-02-25 at 9.23.53 PM

    • Sean
    • 5 yrs ago
    • Reported - view

    It took me a little while to make it work and I think it turned out to be a misplaced quotation mark... not sure though

    • Max_Kondor
    • 5 yrs ago
    • Reported - view

    Wow you actually created a database for this. Thank you so much! It works.

Content aside

  • 5 yrs agoLast active
  • 26Replies
  • 5148Views