Forum Discussion

JoshuaDavis1's avatar
JoshuaDavis1
Qrew Trainee
2 months ago

Consolidate Field Types

Hello, 

Is it possible to consolidate several Text - Multiple Choice field types into 1 field for a report? I believe I would have to create a new field and a text-formula as well. My working theory is below:

[Scope 1 Manufacturer] and [Scope 2 Manufacturer] and [Scope 3 Manufacturer] and [Scope 4 Manufacturer] ="Company A"

We have over 100+ customers, so creating 100 field types for each customer seems to be ineffective option. Thanks! I appreciate this community's help. 

 

formulas
  • DonLarson's avatar
    DonLarson
    2 months ago

    Joshua,

    You said that you needed to pull data of the manufacturer and the scope.   Your formula or the List function that MarkShnier__You  posted can string together the names of the manufacturers.   To get a lot of information I suggest you build the app a little different.

     A Table of manufacturers gives you one source of truth.  You do not have to have Dell in four separate list and maintain them.   You also can keep adding info about the manufacturer and expanding what the Bids know through the relationship.

    The formula you posted does not have any conditionals in it.  Are you looking to evaluate who the manufacture is to show something?  For example:  

    If ( [Related Scope One Name]="Dell", "A value for the report",  "A different value for the report) 

     

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    2 months ago

    Joshua,

    You said that you needed to pull data of the manufacturer and the scope.   Your formula or the List function that MarkShnier__You  posted can string together the names of the manufacturers.   To get a lot of information I suggest you build the app a little different.

     A Table of manufacturers gives you one source of truth.  You do not have to have Dell in four separate list and maintain them.   You also can keep adding info about the manufacturer and expanding what the Bids know through the relationship.

    The formula you posted does not have any conditionals in it.  Are you looking to evaluate who the manufacture is to show something?  For example:  

    If ( [Related Scope One Name]="Dell", "A value for the report",  "A different value for the report) 

     

    • JoshuaDavis1's avatar
      JoshuaDavis1
      Qrew Trainee
      26 days ago

      Don,

      Sorry for the late reply. I'll work on creating a table of manufacturers. 

      The formula you posted does not have any conditionals in it.  Are you looking to evaluate who the manufacture is to show something?

      • Yes, I am trying to evaluate who the manufacture is to show their total cost and sell for a report. For example, I can gather the total cost and sell (screenshots below). I am trying to gather the total cost and sell of a single manufacturer. The problem is I do not have a way to gather this information.
      • DonLarson's avatar
        DonLarson
        Qrew Elite
        26 days ago

        Joshua,

        It looks like you are putting together information from a quote or a bill of materials.  Calculating Total Sell and Total Cost is a common need.   Organizing it by the Manufacturer is also not hard but it depends on the architecture of the application.

        Here is how I would tackle it

         

        On the Project you have Summary Fields that show you the Total Cost and Sell for everything in the Bill of Material

        You also have an embedded report, BOM Report, that you can have summarize the Cost and Sell by the Manufacturer.    You can build that report just about any way you would want, but having everything in a child table to the Project is the key to having this work.

         

    • JoshuaDavis1's avatar
      JoshuaDavis1
      Qrew Trainee
      7 days ago

      Don, 

      I wanted to provide an update for you. Thank you! Your suggestion worked. I appreciate you and Mark for helping me out. 

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 months ago

    Sorry, I would like to help, but I don't think we are understanding your question. Can you explain in words the business use case for what you're trying to do with some examples. Then we can probably help. 

    • JoshuaDavis1's avatar
      JoshuaDavis1
      Qrew Trainee
      2 months ago

      Hey Mark! Thank you. Sorry for the confusion. I'll include some screenshots. In the Bid Scope screenshot, you will see 5 scopes. Our users can include more than one manufacturer under different scopes.

      In the Field Type screenshot, you will see we have 10 field types that correspond to a manufacturer. 

      My goal is to pull data based off the manufacturer and its corresponding scopes.  Currently, I am unable to pull data for one manufacturer. I believe I need to create a new Formula - Text field or a new Text field that includes all of the manufactures.

       

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        2 months ago

        yes, you can combine these using the List function.  The list function combines non blank fields separated by a separator character. If the fields are blank then it does not "separate" them.

        So not tested but try this.

        List("\n", // use a carriage return as the separator for each pair

         List(" - ", [Scope 1], [Manufacturer 1]),

         List(" - ", [Scope 2], [Manufacturer 2]),

        etc

         List(" - ", [Scope 9], [Manufacturer 9]))

         

         

  • JoshuaDavis1's avatar
    JoshuaDavis1
    Qrew Trainee
    2 months ago

    As for an example. I included a screenshot of a formula. If this caused more confusion, please let me know. 

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    26 days ago

    Joshua,

    If you just want to string together the names of the manufacturers, then Mark's solution would give you a column of names.

    A list of the names can be done like this:

    [Manufacturer 1] & " " & [Manufacturer 2]

    This will give you a row with a space between the names.