# Lookup Key Value in database

**URL:** <https://community.dreamfactory.com/t/lookup-key-value-in-database/1157>\
**Category:** Connecting to SQL\
**Created:** [June 3, 2015, 11:24pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157 "2015-06-03T23:24:41Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Boris](https://avatars.discourse-cdn.com/v4/letter/b/74df32/32.png) [@Boris](https://community.dreamfactory.com/u/Boris)\
**Post date:** [June 3, 2015, 11:24pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/1 "2015-06-03T23:24:41Z")

</div>

Can i work with lookup keys in DF database?  
When i put simple value like ‘1’ i get in database value like this: “3BoL5oV75bzrHKdLSyU8Tm75DhA79ScGr9I1l1jIa9o=”. What is this? Can i code and decode this value in sql database?

I need generate lookup keys for user automaticaly by SQL…

---

<div class="post-metadata">

**Author:** ![jeffreystables](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/jeffreystables/32/173_2.png) [@jeffreystables](https://community.dreamfactory.com/u/jeffreystables)\
**Post date:** [June 4, 2015, 8:32pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/2 "2015-06-04T20:32:14Z")

</div>

I’m not sure I follow your example. Can you provide the full API call and response from your DreamFactory instance? Does it behave the same way when tested in API Docs?

---

<div class="post-metadata">

**Author:** ![Boris](https://avatars.discourse-cdn.com/v4/letter/b/74df32/32.png) [@Boris](https://community.dreamfactory.com/u/Boris)\
**Post date:** [June 4, 2015, 9:58pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/3 "2015-06-04T21:58:52Z")

</div>

My question was not related to usage of API call. I want to setup lookup keys for each user. When i do this by DreamFactory admin interface, i’m adding a new lookup key, indicate its name (for exmple, “Key1”) and value (for example, “1”). And its working.

But, i need to create this key automatically for each user, when user registers. I think, i can do it by SQL trigger in database layer and the result, there is not need to change DreamFactory php-scripts. But when i see lookup keys in database table, which where created by DreamFactory admin interface, i see values like “3BoL5oV75bzrHKdLSyU8Tm75DhA79ScGr9I1l1jIa9o=” and i realise, that value was encrypted.

How can i generate lookup keys automatically for each user?

---

<div class="post-metadata">

**Author:** ![Boris](https://avatars.discourse-cdn.com/v4/letter/b/74df32/32.png) [@Boris](https://community.dreamfactory.com/u/Boris)\
**Post date:** [June 8, 2015, 2:06pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/4 "2015-06-08T14:06:04Z")

</div>

I want to setup lookup keys for each user. When i do this by DreamFactory admin interface, i’m adding a new lookup key, indicate its name (for exmple, “Key1”) and value (for example, “1”). And its working.

But, i need to create this key automatically for each user, when user registers. I think, i can do it by SQL trigger in database layer and the result, there is not need to change DreamFactory php-scripts. But when i see lookup keys in database table, which where created by DreamFactory admin interface, i see values like “3BoL5oV75bzrHKdLSyU8Tm75DhA79ScGr9I1l1jIa9o=” and i realise, that value was encrypted.

How can i generate lookup keys automatically for each user?

---

<div class="post-metadata">

**Author:** ![jeffreystables](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/jeffreystables/32/173_2.png) [@jeffreystables](https://community.dreamfactory.com/u/jeffreystables)\
**Post date:** [June 8, 2015, 7:54pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/5 "2015-06-08T19:54:59Z")

</div>

Our engineers mocked up an example for you.

This script is designed to trigger on `user.register.post.post_process`. Guest access and open registration will need to be enabled and configured. System/user script access will need to be added to the selected guest and open registration role. Then try registering a new user.

```json
var user_data, result;

user_data = {
    "lookup_keys": [
        {
            "name": "somekey",
            "value": "somevalue",
            "private": false,
            "allow_user_update": false
        }
    ]
};

result = platform.api.get("system/user?fields=id", {"filter": "email='" + event.request.body.email + "'"});
var_dump(result);
if (result.record && result.record.length === 1) {
    user_data.id = result.record[0].id;
    result = platform.api.patch("system/user", user_data);
    var_dump(result);
}

```

---

<div class="post-metadata">

**Author:** ![juniorconte](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/juniorconte/32/1007_2.png) [@juniorconte](https://community.dreamfactory.com/u/juniorconte)\
**Post date:** [November 13, 2015, 1:34pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/6 "2015-11-13T13:34:15Z")

</div>

Thanks @jeffreystables, it did really works.

ps\*. DF 1.9.4

---

<div class="post-metadata">

**Author:** ![rdubya152](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/rdubya152/32/492_2.png) [@rdubya152](https://community.dreamfactory.com/u/rdubya152)\
**Post date:** [December 5, 2015, 1:59pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/7 "2015-12-05T13:59:25Z")

</div>

nice question @juniorconte. @jeffreystables is it possible to query user lookup using php? i know the platform object isn’t available for PHP (yet?), but I’m hoping there’s some other way to query user lookup keys on the fly through a custom script.

---

<div class="post-metadata">

**Author:** ![rdubya152](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.dreamfactory.com/rdubya152/32/492_2.png) [@rdubya152](https://community.dreamfactory.com/u/rdubya152)\
**Post date:** [February 15, 2016, 9:48pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/8 "2016-02-15T21:48:00Z")

</div>

Does there happen to be a PHP iteration of this code? I’m trying to work with this V8JS example, but Ubuntu (linux) is giving me a hard time with the ‘module “lodash” cannot be found’ error.

---

<div class="post-metadata">

**Author:** ![ssamuel](https://avatars.discourse-cdn.com/v4/letter/s/df705f/32.png) [@ssamuel](https://community.dreamfactory.com/u/ssamuel)\
**Post date:** [March 30, 2016, 6:32pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/9 "2016-03-30T18:32:59Z")

</div>

Is there a way to retrieve these with GET system/user? I tried the PATCH system/user with this payload through test\_rest:

[{“id”:32,“lookup\_keys”:[{  
“name”:“mykey”,  
“value”:“myvalue”,  
“private”:false,  
“allow\_user\_update”:true  
}]}]

It appears to work correctly, the response is a resource object with the same ID. The value doesn’t seem to show up anywhere, making me wonder if it did anything at all. It’s not in the user lookups admin (that’s where it should be, right?) and GET system/user doesn’t return it either. Am I looking in the right place(s)?

Is there a chance for a more deterministic and better-documented API for lookups? This is critical functionality for my application and I would imagine for many others’ applications as well. I may be able to help write a branch or even some documentation.

---

<div class="post-metadata">

**Author:** ![ssamuel](https://avatars.discourse-cdn.com/v4/letter/s/df705f/32.png) [@ssamuel](https://community.dreamfactory.com/u/ssamuel)\
**Post date:** [April 5, 2016, 3:20pm UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/10 "2016-04-05T15:20:54Z")

</div>

If you’re missing Lodash, you can get it at [lodash.com](http://lodash.com). There should be a CDN link, add a script include in your master template (or the template for that page). It’s a good library for manipulating arrays and objects, I use it often when I do JS-heavy sites.

Do you still need PHP code?

---

<div class="post-metadata">

**Author:** ![AdrianZ](https://avatars.discourse-cdn.com/v4/letter/a/ecb155/32.png) [@AdrianZ](https://community.dreamfactory.com/u/AdrianZ)\
**Post date:** [August 31, 2016, 3:23am UTC](https://community.dreamfactory.com/t/lookup-key-value-in-database/1157/11 "2016-08-31T03:23:46Z")

</div>

@ssamuel I’ve been experiencing the same issue as you and have found that in DreamFactory 2.1.2 it appears that “lookup\_keys” has now been super seeded with a related table called “user\_lookup\_by\_user\_id”. Please try the following:

```
var user_data, result;

user_data = {
    "user_lookup_by_user_id": [
        {
            "name": "somekey",
            "value": "somevalue",
            "private": false,
            "allow_user_update": false
        }
    ]
};

result = platform.api.get("system/user?fields=id", {"filter": "email='" + event.request.body.email + "'"});
var_dump(result);
if (result.record && result.record.length === 1) {
    user_data.id = result.record[0].id;
    result = platform.api.patch("system/user?related=user_lookup_by_user_id", user_data);
    var_dump(result);
}

```

You will find that you can now do a GET Request with system/user?related=user\_lookup\_by\_user\_id which will return the fields you are after.

Hope this helps.