0

Can we link a field to a table view?

Hi, 

sorry to post here but i m working on a db and i dont know where to get the information that i need. I want to have a table with client information (id, name, phone number) for exemple.

I would like to have a table with all phone numbers and other table with the client linked with a phone number and with a date. So for exemple, john 514-555-5555 january 1st , john again with another number and another date....

then I would like to link the field 'phone number' in the client table to the most recent number for this client.

so if john change his number, I can have an historic of his past number... each number will be assign only once so I need to know if it s already assign but it s for the form part...

can we link field to table view? If so, how? if not, I can I do ?

Hope I explain it well! 

thank you and have a nice day!

33 replies

null
    • Mconneen
    • 4 yrs ago
    • Reported - view

    I am a little confused by ... "each phone number will be assigned only once"...  Are you saying that John's number cannot match Mary's phone number?   Or are you saying that John cannot re-use a prior phone number?  What if both John and Mary are clients.. and they are married / cohabitate ? 

    You are 90% there with the out of the box "Contacts" template database.  You can add logic in the phone numbers trigger after update to determine if the number is a duplicate.. and if so.. set it back to the prior number and pop up an alert.   If the new number is NOT a duplicate, copy that number to a hidden / history table that is also linked to the contact by creating a new history.. assigning it the phone number value.. and setting it's Client to this (the current client row).. 

    Hope that helps.. 

    Contacts 

    • plafontaine
    • 4 yrs ago
    • Reported - view

    HI,

    THANKS FOR THE REPLY! i STILL HAVE A QUESTION, IF i HAVE MY OWN PERSONAL CLOUD, CAN I SAVE MY DATABASE ON IT AND CAN I GRANT ACCESS TO IT FROM MY WIFE CELLPHONE FOR EXEMPLE? iT S ON ANOTHER APPLE ID BUT IF WE ARE NOT ON ICLOUD BUT PERSONNAL CLOUD...IS IT POSSIBLE?

    • Mconneen
    • 4 yrs ago
    • Reported - view

    @plafontaine.. I defer to Ninox Support on that one..  I do recall some threads discussing where Ninox stores the database locally.. I do not have this use case.. so never really worried about it.   Good luck tho! ;) 

    • Birger_H
    • 4 yrs ago
    • Reported - view

    You can manually export to any place. And you can import the Ninox database from any place. Please note that there will be no synchronisation this way.

    Birger

    • plafontaine
    • 4 yrs ago
    • Reported - view

    thank you!

    I have another question about the user interface. I mean, i want to built a form that give to the user multiple possibility. I wish to have real time stats shown, searching tool and data collection...

    What can you tell me about ninox forms that way? 

    • Mconneen
    • 4 yrs ago
    • Reported - view

    @plafontaine... You should start another thread / question as opposed to adding a different topic to this one.. At least my two cents.. :)   That said..  here is an example of a dashboard.. aka "real time stats" . 

    kpi

    • plafontaine
    • 4 yrs ago
    • Reported - view

    Thank you, very nice, but what if I wish to show a top 10 sellers list? and a top 10 best client (amount spent or store visit)..etc ? I wish to open my form and see a lot of details like this (a lot of top list)...

    • Mconneen
    • 4 yrs ago
    • Reported - view

    I have a different application that tracks about 37 different KPIs.. So.. I built a KPI Template table (has all 37 KPI descriptions).. then I built a Dashboard table.. and put logic behind the "Populate KPI" button that builds the main KPI table.   You could do something similar. 

    kpi

    • plafontaine
    • 4 yrs ago
    • Reported - view

    Hi,

            Thank you for the reply! I m looking but doesnt find how to create dashboard and kpi... Can you explain what it is and how to create it? Ideally, i would like to have a kind of userform like excel or access. The userform can show some view in each pages, and can also have somme button and textboxes to fill data. So I would like to be able to click on ''new cooking class'' page and then have the possibility to enter information, like client, products they bought, payment method, host name...etc. after that, when click the add button, each textboxes and scrolldown menu that was filled are linked to table and field... so it would add an entry in the cooking class table with host name and other information, but also take the client list and their purchased and add an entry in other tables that correspond...

    I just dont want to add each entry 1 by 1...like going to client table, if client do not exist add it, then go to purchased table and add his purchased, then go to cookware_client table and add a presence in this cooking class for this client...and before that add this cooking class...etc...

     

    I m looking for an interface, (excel and access userform are good exemple because 100% costomizable) this interface will show information and be very friendly to entering new data to database...like adding new client, start writing and auto filter, so if client name exist you can check if it s the right one and select it and if not you can simply finish typing his name and adresse...etc and at the end when you click a button it simply add all information in every table...

    So you have all information by pages , maybe (best sellers, best cooking class by sell, best client by frequency, best host by number of client, best host by selling....etc) I could also planify somme kind of following with client let say 2 weeks after their purchased to see if their are satisfyed , so I could add a pages with a listbox that countain client name and other information to makes a following, maybe even a last call date and comments...so i know that he was not at home last week so he is in the list again...or he was there and he told me that he would like to host a class, so i can keep following...etc.

     

    So first of all, what is kpi and how you doing it? same with dashboard!

    also, do you see any way to do what i want with the details i added? I see that there are zapier and other app to merge ninox and google sheet or excel... but it s like if ninox get modifyed add a row in google sheet...or if a row added in google sheet add an entry in ninox...so i cant show ninox view within google sheet...

     

    Sorry to writing a lots of things ! Thanks again for your good support!

    • plafontaine
    • 4 yrs ago
    • Reported - view

    another things, I will make a table with all adress and make another table with client info (name, date of birth) and make another table wich linked client and their respectives adresse.I also have a table that contain client and adresse with date.

    on access it have query, that create a kind of table with all client and all adresse with criteria. So I can make a query with all client and their most recent adresse. From there, I can make a table that contains all client, name, adresse and other things like phone number...etc

     

    But with ninox, I only have table and view. So I have a table with client...another one with adresse...and another one for client, adresse and date. 

    The problem is that if I will get client last adresse, i have to manually tell my table entry: client 1 is linked to client_adresse_date #3 and adresse...etc...it does not seem to take all client number and all client_adresse_date entry and then add criteria...

     

    How can i do? I tough that I was understanding! :)

     

    Thanks again!

    • Mconneen
    • 4 yrs ago
    • Reported - view

    Did you start with the Contact template? 

    Contact

    The tricky part will be how you want the new contact add to work..

    • plafontaine
    • 4 yrs ago
    • Reported - view

    Good point! Look like a good idea, but when i have a phone and a date inside the phone table, I m not able to show the phone number in the contact table. I can show the max date but if I show another column, how can I show the number corresponding to the date ? I would like the column to be linked to the corresponding field, because I can show a number different from the matching date... 

    I can also make a big table with all data but I must duplicate the contact each time I got new changes (phone, adresse, email...) and then make a view for each field (last phone, last email, last adresse...) take a lot more space!

    Is there a way to do that?

    • Mconneen
    • 4 yrs ago
    • Reported - view

    Rather than simply "linking" to the contact phone composite table, create a view and show the row where EffectiveDate = max(EffectiveDate).   

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    i was not aware that we could make a view within composite table... can you gives me more detail on how to do it please?

    Thanks again!

    • Mconneen
    • 4 yrs ago
    • Reported - view

    OK.. Using the Contact database template... and let us assume that the most current 'Contact Phone'.'Modified on' date represents the "current number" for that label ... (you can use your own effective date if you wish)... 

    I added a column to the 'Contact Phone' called 'Current Number' as follows.. 

    currentNumber

    On the Contact form.. I added a View element as follows:

    theViewFormula

    which yields..   The following... NOTE.. because the above uses t.Phone .. meaning this.Phone on the contact.. Ninox has already filtered the 'Contact Phone' list to 'Contact Phone'[number(Contact.id) = number(t.id)]  for me.. so that is not required.. 

    theView

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    thank you! Seem to work...but I have a little problem! I dont know why but I change the label name and also updated my formula with the right names... then I addes a field in the contact table with this formula:

    if (concat(Phone.Label) = "Maison" and concat(Phone.ACTUEL)) = true then
    concat(Phone.Number)
    end

    so I hope to show a phone number if it s actual and the label is ''Maison''. But the number already in the phone table stay ''no'' in actual field even if I added a date for last changes (I have change your formula to take that field) and when I add some number to get an actual number, the number disapear from the contact table! Seem to do the opposite that I want... 

    Do you have any solution for this? 

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    it s not making the difference between label too. It takes the first actual number. Is there a way to have each label actual number? And like I said in previous post it still dont like single phone number entry (stay not actual).

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    another things, if i edit the date and make it ''now'' for a number, the table will make it actual but keep last actual to true. It seems to not recalculate on changes...

    • Birger_H
    • 4 yrs ago
    • Reported - view

    If you create a formula with the function 

    –––
    now()
    –––

    it will constantly update. If you fill a regular field it will not.

    Birger

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    how should i do that? I already have a formula in my field. And how to modify my formula to have the max date for every different label? 

    here my formula:

    let t := this;
    t.DERNIER_CHANGEMENT = max((select 'Contact Phone')[number(Contact.ID) = number(Contact.ID) and text(Label) = text(t.Label)].DERNIER_CHANGEMENT)

    dernier_changement = date of changes

    other fields are from contact templates

     

    thanks!

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    IS there a way to take time in consideration when using max(date) ?

    for my last question, I tryed this:

    let t := this;
    t.DERNIER_CHANGEMENT = max((select 'Contact Phone')[number(Contact.ID) = number(Contact.ID) and text(Label) = "Maison"].DERNIER_CHANGEMENT)

     

    But it return yes only if it s "Maison" and the max date of all label, not just "Maison".

    I keep searching... if you can help me on that one! Wish to get max date for each label (if maxdate among all "MAison" label then ruturn yes and if maxdate among all "cell" label then return yes...etc) a kind of array because I wont enter all label value manually!

    Thanks!

    • Mconneen
    • 4 yrs ago
    • Reported - view

    My previous example (see page 2) ... provided max date for each label. 

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    Hi,

           Sorry but I just do like you said, I just changed the 'modified on' by ma field name for the date and it give me only the maxdate for the entire label list...I dont know why...it s the same thing as you...

     

    I ll try to start another db with contact template and redo all that to see!

    • Mconneen
    • 4 yrs ago
    • Reported - view

    In your above syntax of. 

    t.DERNIER_CHANGEMENT = max((select 'Contact Phone')[number(Contact.ID) = number(Contact.ID) and text(Label) = "Maison"].DERNIER_CHANGEMENT)

    Because you have 

    and text(Label) = "Maison"]

    it will only return the max of Maison...

    • plafontaine44
    • 4 yrs ago
    • Reported - view

    yes that was a test...but i must have done something wrong because I restart it from scratch and it seem to works now! 

    I m trying to display each label number in the contact table.

    thank you!

Content aside

  • 4 yrs agoLast active
  • 33Replies
  • 8952Views