0

how to project this db

Hello need to manage my small business. Every day X products goes out from my store to my clients, and Y products return (my client has not sold them all). So i think a table for products i sold, and a table for clients. Then my doubt is how to manage the sold product and the returned products Shall i do 2 different tables, one for sold one for returned? That means that i wil have a table with

date client product sold quantity

and the other with product returned insted of sold

At the end i must be able to see the overall product sold/retruned by clients and percentage

And also to see by products the relation sold/returned

For this i see the report/graphic available don't allow me to get data from 2 different tables..so how can i do that?

101 replies

null
    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    Ok.The dropdown (or radio) with 100-150 client on a mobilephone (the operator is moving around and will use a smartphone) it isn't very hand.

     That is very true. I’m glad to hear you have so many customers.

    I can do the delivery section to look like the kitchen, just that the button would do slightly different things.

    I’ll also see how to do multiple scanning, so your boss can scan to her hearts content.

    • stef_idea
    • 1 yr ago
    • Reported - view

    🙂 i don't know really how many clients, i believe that 50 is even overestimated..but she think for the future nd she is confident probaly....anyway even 30 on a smartphone it s not nito deal with it

    • Fred
    • 1 yr ago
    • Reported - view

    Here is my latest sample.

    The Kitchen tab now has two parts, you can scan a single item and enter a specific quantity or you can "scan" in multiple product ids. A new button will now create a new Movimenti record and respective Vendite records or it will add new Vendite records if there is already a Movimenti record, or it will update current Vendite records with the product ids that were scanned in.

    The Delivery tab allows you to type in some letters and a radio list will show up with clients that start with the letter. Once you select a client then it will bring up all deliveries to the client. You can also select a date so you only see deliveries from that date.

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred just uploaded..i'll have a look tomorrow, that it has many new things to see..i had just give an eye for now!

    • stef_idea
    • 1 yr ago
    • Reported - view

    ok i could give a go...

    the kitchen seems very smart, i have 2 possibilities to insert product, both with scanning right?

    the delivery i still see it complex for the operator that works on the fly through a smartphone

    For what i see he needs to:

    digit 1 or 2 letters

    select a client

    select an order

    insert the return

    Everything is made manually, at least it seems so

    This delivery table should really be as smart as kitchen. I understand the logic of it, and i cannot say anything about it..it works flawlessy and efficent, but  i am really concerned for the operator that should use it...

    One question..why you want to put the returned items in the order where it belong?it is a complexity for the operator and also, for the office usage at least, is not needed. Would just be enough if he could insert with a barcode readin all the products he has to bring back?

     

    p.s.

    that curtain menu in the bottom "customer" it is not for operator use right..it is just for your developing pourposes?

      • Fred
      • 1 yr ago
      • Reported - view

      stef. idea for the driver, do you want single item and qnty or multiple scan?

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred mmm good question...asi think the return items should not be so big, may be the multiple scanning should be the best solution..and the aim is to have less and less return....so that should be the way

    • Fred
    • 1 yr ago
    • Reported - view

    Ok take a look at v3. I changed delivery to returns. There is the item/quantity options and the multi scan option. I left the view element so you can see it working.

    In the end should give you an idea if Ninox is right for your requirements.

    • stef_idea
    • 1 yr ago
    • Reported - view

    ok much better..

    One thing:

    after some test seems did not work anymore correctly..but i ll test it more

     

    So i think this is a good starting point for doing some statistics

    • stef_idea
    • 1 yr ago
    • Reported - view

    now seems theproblem is the barcoding readin on a smartphone. I am using android and have installed a barcde readin app. When i go to the cusotmer id field for example...i open the scanner, it correctly read..but there is nothing in the field...

    The scanner wotk as he will feed other app, mail, watsapp ...it  just don't work in ninox

      • Fred
      • 1 yr ago
      • Reported - view

      stef. idea sadly there is nothing I can help with on Android or another product. In iOS the scanning tool is built into Ninox. You may want to email Ninox directly.

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred i have contacted them..just tell me, with apple, you didn't need to do anything special to have barcode reading, right?it just works?

       

      Ok how can we proceed now for data analysys, graph..and similar things?

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    with apple, you didn't need to do anything special to have barcode reading, right?it just works?

     that is correct. no special software needed.

    • stef_idea
    • 1 yr ago
    • Reported - view

    ok thanks for claryfing...hopefully ninox can sort the problem

     

    For the graph/statistici the table where start from is MOVIMENTI i suppose?

    From this table we should make some query

    Ideal would be if we can select parameter from which make query, among date, clients and products

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    For the graph/statistici the table where start from is MOVIMENTI i suppose?

    That is correct. Take another look at the db file I upload a few days ago to see samples of some codes.

    • stef_idea
    • 1 yr ago
    • Reported - view

    i am looking at it, i should need to introduce more parameter..in that table there is time and sold/return

    But if i want to introduce in addiion to these, client and product id?

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    But if i want to introduce in addiion to these, client and product id?

     Take a look at the DB with the Quick View table to see how to add user selected parameters.

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred i was looking at it yes..one question

      is to possible tyo have a kind of form (like those in access for example)

      where i can select form a dropdown menu the client, the product id and the range of time and have returned different statistics?

       

      p.s. anyway..it is not the form itself th eimportant..but the possiiblity to have more parameters where to choose form

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    where i can select form a dropdown menu the client, the product id and the range of time and have returned different statistics?

     Yes you can do this. The Quick Form table shows you an example of how to do it with a month selection. But you can do dynamic choice fields so you can get data from your clients, products and time.

    • Fred
    • 1 yr ago
    • Reported - view

    If you still think the return process is too cumbersome for the driver, can the driver scan the info into a spreadsheet that you can import daily?

      • stef_idea
      • 1 yr ago
      • Reported - view

      Fred it could be a possibility. Boss said that she would like to have the driver update inrealtime the db..but for what i have seen this possibility is only theory..because to have a real update, the user must refresh everytime the db. So if the driver add a record at client place...in the office, if they are working at the db they don't see it unless they do a refresh..is that correct? The import later has also another benefit..the driver could use a real barcode scanner right? There are barcode scanner that store in a local spreadsheet?

      • Fred
      • 1 yr ago
      • Reported - view
      stef. idea said:
      but for what i have seen this possibility is only theory..because to have a real update, the user must refresh everytime the db.

      To the best of knowledge, if you use the Cloud version then everything is updated in realtime. 

    • Fred
    • 1 yr ago
    • Reported - view

    Take a look at v4. I’ve updated the Quick View table to show one way of doing the data analysis.

    there is a hidden field called gatherData that figures out the which fields got used and then to find the appropriate data. 

    with this method, you can then create the summary fields and just reference gatherData to get your results. Like Total Sold would be:

    sum(gatherData.Vendite.Sold)

    The code for gatherData, is the most elegant way about it but it works.

    let t := this;
    let startCheck := if 'Start Date' != null then 1 end;
    let endCheck := if 'End Date' != null then 2 end;
    let yearCheck := if year != null then 3 end;
    let monthCheck := if Month != null then 4 end;
    let custCheck := if Customer != null then 5 end;
    let dataEntryCheck := [startCheck, endCheck, yearCheck, monthCheck, custCheck];
    let gatherMovimenti := (select Movimenti);
    switch true do
    case contains(dataEntryCheck, 3) and contains(dataEntryCheck, 4) and
    contains(dataEntryCheck, 5):
        gatherMovimenti[year = text(t.year) and monthNum = t.Month and '2_Clients' = number(t.Customer)]
    case contains(dataEntryCheck, 1) and contains(dataEntryCheck, 2) and
    contains(dataEntryCheck, 5):
        gatherMovimenti['Order date' >= t.'Start Date' and 'Order date' <= t.'End Date' and
            '2_Clients' = number(t.Customer)]
    case contains(dataEntryCheck, 1) and contains(dataEntryCheck, 2):
        gatherMovimenti['Order date' >= t.'Start Date' and 'Order date' <= t.'End Date']
    case contains(dataEntryCheck, 1) and contains(dataEntryCheck, 5):
        gatherMovimenti['Order date' >= t.'Start Date' and '2_Clients' = number(t.Customer)]
    case contains(dataEntryCheck, 2) and contains(dataEntryCheck, 5):
        gatherMovimenti['Order date' <= t.'End Date' and '2_Clients' = number(t.Customer)]
    case contains(dataEntryCheck, 3) and contains(dataEntryCheck, 4):
        gatherMovimenti[year = text(t.year) and monthNum = t.Month]
    case contains(dataEntryCheck, 3) and contains(dataEntryCheck, 5):
        gatherMovimenti[year = text(t.year) and '2_Clients' = number(t.Customer)]
    case contains(dataEntryCheck, 4) and contains(dataEntryCheck, 5):
        gatherMovimenti[monthNum = t.Month and '2_Clients' = number(t.Customer)]
    case dataEntryCheck[= 1]:
        gatherMovimenti['Order date' >= t.'Start Date']
    case contains(dataEntryCheck, 2):
        gatherMovimenti['Order date' <= t.'End Date']
    case contains(dataEntryCheck, 5):
        gatherMovimenti['2_Clients' = number(t.Customer)]
    case contains(dataEntryCheck, 4):
        gatherMovimenti[monthNum = t.Month]
    case contains(dataEntryCheck, 3):
        gatherMovimenti[year = text(t.year)]
    end
    

    Line 1: assigns the current record into the variable “t”.

    Lines 2 - 6: assigns a variable for each possible selection field and then assigns a unique number if the field is NOT null.

    Line 7: creates an array with the variables from lines 2 - 6.

    Line 8: get all of the data from the other table into a variable to be used later

    Lines 9 - 39: uses the switch function to check the array from line 7 to figure out what combination of fields we need to filter our data.

    I was reminded, from testing, that a switch function goes down the list and stops at the first instance of true. Which means you need to put the multi selection check first before the single selection check otherwise it will always stop at the single since that will return true.

    In other words I need to put the check for Start Date and End Date and Customer before I check for Start Date and End Date and before I check for Start Date.

    I am hoping others can find a more elegant (fewer lines of code) way of doing this.

    • stef_idea
    • 1 yr ago
    • Reported - view

    aahh i miss this notification of the new db..i am gonna try it. Meanwhile i was trying to see about the barcode reading in android. Ninox doesn't says about android/apple..i follow this procedure

    https://docs.ninox.com/en/script/function-overview/functions/barcodescan

    but the thing is that when the app on  y phone read the code it is not transfered on the field..so even this procudre it s of no use. Wrote twice at ninox but no reply yet...

      • stef_idea
      • 1 yr ago
      • Reported - view

      stef. idea UPDATE!

      I didn't know that there is a app for NINOX..i installed it now and form the app the barcode reading works! Good Things. I was trying the multiple reading...it says i should press the spacebar for multiple entry..but this seems to do not work on phone..is it?

Content aside

  • 1 yr agoLast active
  • 101Replies
  • 445Views
  • 3 Following