Select Database Group ID



  • I am trying to save some data to a database - if i insert the data with no group id it inserts it into the ALL group.

    But i want to insert it into a specific group - and there the is the field called "Group id. Leave blank to insert in last created group." - if that is empty it inserts into the last created group as the description says - if i enter the group name here it does nothing - and i can't find the actual GROUP ID of each group, i don't have anything like this in BAS, just group names, NO GROUP ID...

    Where do i find the GROUP ID?



  • @andykaufseo I also have this question, did you find the answer



  • not yet, no one wants to answer or no one seems to know how to do it (if it's even possible)...



  • @andykaufseo I just figured it out kinda. You have to create a new group inside the script and then it will set a group id and insert record will send the data there.

    Only problem is it will create a new group everytime.

    It would be a lot better if we couuld find the actual group id. It seems like it is just the name of the group but when i try to use that it doesnt send anyhting



  • yes, with create group it works, but that's not what we need, we don't need to create a new group every time but use an existing group...



  • What I did to solve this in the past is one of two ways,

    • Creating a new table inside the database with "Name" (String) | "ID" (String) and adding the group's name & id when ver it's created. Calling them into a List, Object, etc at the OnApplicationStart (readfile "firstrun.txt" with value of "true/false" if true create all groups that are needed else read database to variable) No point in reading the Database each time for the ID unless you plan on changing it.

    • Creating a config file that's read using the project_path() function.
      To change a [[GLOBAL:JSON_OBJECT]] value you need to call the JSON outside of BAS variable change the value, then back to BAS & save the [[GLOBAL:JSON_OBJECT]] to file. Like so..

    var obj = [[GLOBAL:JSON_GROUPINFO]];
    obj[([[GROUP_NAME]])] = {"ID": [[GROUP_ID]]};
    PSet("basglobal", "JSON_GROUPINFO", JSON.stringify(obj));
    

    You can change the value easy on local variables

    [[JSON_OBJECT]][([[GROUPNAME]])] = {"ID": [[GROUP_ID]]} // Creating the ID Object
    // 3 below is to CHANGE (not create)
    [[JSON_OBJECT]][([[GROUPNAME]])].ID = [[NEW_ID]] // (For variables as the name since [[VAR1]][[[VAR2]]] gives a JSON error (x3 [[[ & ]]] )
    [[JSON_OBJECT]].MyGroupName.ID = [[NEW_ID]]
    [[JSON_OBJECT]]["MyGroupName"]["ID"] = [[NEW_ID]]
    

    To get the project_path it will return the .xml file too. Here's a snippet to take that out.
    Value AS Expression
    (project_path()).match(/(.*)[/]/)[1]||''
    Can add more Regex to allow the path be outside the AppData just encase you update the bot with a new BAS Studio Version.

    Let me know if this helps!

    Allister ☯



  • @Allister I definitely am a bit confused on how to do this from reading your post. So the only way to get the "Insert record" feature to work is to do some unintuitive methods?

    I was trying to follow tutorial #3 but got stuck at the insert record part. Just want my data to be moved to the correct group and I have the rest of the script working.

    So let me see if I have this right based off of what you said.

    1. Make a new table in my current database.
    2. Name the columns in the new table "Name"(String) and "ID"(string)? Or is that supposed to be the name of the table itself?
    3. Adding the groups name and id somewhere (What is a ver?) (Im really not getting this part)

    Im not seeing how making a new table will allow me to write the data to my previous table I am trying to write to



  • @cleez said in Select Database Group ID:

    @Allister I definitely am a bit confused on how to do this from reading your post. So the only way to get the "Insert record" feature to work is to do some unintuitive methods?

    Sorry, for the lack of explanation. I've created a example for you & everyone else who needs it. I threw this together real fast so it's not that neat but it gets the job done. :)

    Make sure not to run it with "first run = true" more then once. (why I set default to false). This can be fixed with simple checks on the Group Table. Select --> if (groupname exist).. etc and then you can drop the resource bool.

    @cleez said in Select Database Group ID:

    Im not seeing how making a new table will allow me to write the data to my previous table I am trying to write to

    on the "Show Database" you'll need to manually create the table. After that, the script will create the groups that's need (hard coded, or resourced friendly). If you have any questions about the example let me know! :)

    Allister ☯

    0_1506976172410_cleez-database-example.xml



  • @Allister Okay I think I am getting what you are saying about the program making the groups as needed and putting the apprioprate data in there. This will only make the groups on the first run correct? Afterwards it will just put the data in the groups?

    Also is it possible to copy and paste this script into one I have already created?



  • @cleez said in Select Database Group ID:

    This will only make the groups on the first run correct? Afterwards it will just put the data in the groups?

    Correct, or like I said you can have it check if the groups already exist, if they don't create them else it will create a Global Json Object. One thing I forgot to mention, when creating Global Json Object at run time, you'll need to change the spaces (if any) to "_" or "-" or completely remove them. This can be done with a split or regex or a replace.

    @cleez said in Select Database Group ID:

    Also is it possible to copy and paste this script into one I have already created?

    Yes just open the project in a new BAS studio and you can copy the commands over. You'll need to edit each Database command and change the values & table since the scripts database ID's might be off. (Database Schema in the .xml at the bottom)

    Allister ☯



  • @Allister Alright cool, I tried to run it just to test and its saying

    TypeError: Result of expression 'JSON.parse(P("basglobal", "JSON_GROUPINFO") || '""').ActiveAccounts' [undefined] is not an object. Line number:2 During execution of script { _sa(44666115); var group = (JSON.parse(P("basglobal", "JSON_GROUPINFO") || '""').ActiveAccounts.ID) if(group.length == 0) group = P("_bas_table_" + (1), "_last_group") VAR_NEW_RECORD_ID = _db_add_record(group, [2,VAR_USERNAME,3,VAR_PASSWORD], 1) _next_or_section(123113051,function(){ })} 
    


  • @cleez

    Doesn't look like you created the Object for ActiveAccounts
    Look on the example. OnApplicationStart ---> (close to bottom) ----> Foreach (GROUP_LIST)

    var obj = [[GLOBAL:JSON_GROUPINFO]];
    obj[([[GROUP_NAME]])] = {"ID": [[GROUP_ID]]};
    PSet("basglobal", "JSON_GROUPINFO", JSON.stringify(obj));
    

    In the example it selects all the groups in Group Table, then loops through them, parsing the line and "creating" the object for each group.

    Allister ☯



  • @Allister Weird enough i was using the example without changing anything just to try it. IS it possible I didnt set the table up correctly? Should I just delete all this and start over fresh

    picture of the code
    http://prntscr.com/gshryu



  • @cleez

    Hmmm. I downloaded and ran it. Worked for me. Did you run it fully with "First Run?" set to true?
    https://i.imgur.com/XbyhOWK.gifv



  • @Allister Yea I set it to true, its goes as far as making the groups before it stops. Should I be manually making the database and table before I run the script(Thats what I did before I ran it the first time)

    Screenshot
    http://prntscr.com/gshvc7



  • @cleez
    On the Data Manager ----> File ---> Delete All Data
    Then run it like I did in the gif I added. to the post above.

    The script will create the Groups & add the groups with the ID's to the "Group" table if First Run? = true. After, it will selected all records in the Group Table, create the Global Object & then add each Group to the Global Object as an Object.

    There should be no need to manually create anything in the database since the script has the Database Name & Schema in the xml file. Try closing the BAS Studio & deleting the database in C:\Users\{YOURPCNAME}\AppData\Local\BasDatabases\Database.19545. Then restart BAS.



  • @Allister I think I figured out what I did wrong. The first time i ran it i didnt open the database so it could restart after opening the XML file. Then I ran the script for first time user etc and restarted the database. Im pretty sure somewhere in there I screwed something up for that profile.

    I redownloaded your example and deleted the old database and reran it and it all worked this time around.

    Thank you for all the help, im going to play around with this for the next few hours and if i run into trouble ill @ you



  • @cleez

    Oh yes, if you open a new project with a database you need to restart that certain Studio (when having multiple BAS Studio's running). ^_^ I forget that sometimes too. In-fact I spent 2 hours on a project debugging the sucker only to find out that was the cause of all my problems.



  • @Allister Haha that sounds annoying, last night I spent a few hours wondering why my database wasnt using both columns until i realized you need to add text to the description part of the column string



  • @andykaufseo I found an easier way